Table Colum Alter with Relation

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Table Colum Alter with Relation

Author

Topic

binto
Yak Posting Veteran

59 Posts

Posted - 2010-06-30 : 02:29:58

Hi,
I want to alter one column of a table,in which the same column is having relation with 3 other tables.How can I alter the datatype?

Thanks & Regards
Binto Thomas

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-30 : 04:51:31

Follow the below steps to get it done -

1. Get the Script with the following Query and
save it somewhere to recreate foreign keys ( make sure u are keeping it safe otherwise you may lose all the foreign keys )

Specify PrimaryKey table name in the following query



SELECT	' ALTER TABLE '+FK.TABLE_NAME+
		' ADD CONSTRAINT '+C.CONSTRAINT_NAME+' FOREIGN KEY'+
		' ( '+CU.COLUMN_NAME+' ) '+
		' REFERENCES '+PK.TABLE_NAME+
		'( '+PT.COLUMN_NAME+' )' ForeignKeyScripts
FROM	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
		INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
		INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
		INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
		INNER JOIN 
		(
			SELECT i1.TABLE_NAME, i2.COLUMN_NAME
			FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
			INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
			WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
		)	PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE	PK.TABLE_NAME IN ('Primary key table name')

2. Run the following Query to Drop the Foreign key



DECLARE @SQL AS VARCHAR(MAX)
SET @SQL = ''
SELECT	@SQL = @SQL + ' ALTER TABLE ' + FK.TABLE_NAME +
		' DROP CONSTRAINT '+ C.CONSTRAINT_NAME
FROM	INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
		INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
		INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
		INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
		INNER JOIN 
		(
			SELECT i1.TABLE_NAME, i2.COLUMN_NAME
			FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
			INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2 ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
			WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
		)	PT ON PT.TABLE_NAME = PK.TABLE_NAME
WHERE	PK.TABLE_NAME IN ('Primary key table name')

EXEC ( @SQL )

3. Alter the columns ( datatype as u want ) in all the table ( primary key table and foreign key tables )

4. Now Execute the Script which we kept in First Step

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources