Below T-SQL QUERY helps to get DROP and CREATE script for all FOREIGN KEY constraints in SQL SERVER. Feel free to use it if needed, Kindly let me know in case if you got a better and easy way of doing it (do not say 😊 that it can be done using SSMS, right-click on the database-->Tasks-->Generate Scripts...).
SELECT CONSTRAINT_NAME,
'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = ''' +CONSTRAINT_NAME +''') ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) + ' DROP CONSTRAINT ' +QUOTENAME(CONSTRAINT_NAME)+' ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' WITH CHECK ADD CONSTRAINT '+QUOTENAME(CONSTRAINT_NAME)
+' FOREIGN KEY('+[COLUMNS]+' ) REFERENCES '
+ QUOTENAME([REFERENCED_TABLE_SCHEMA])+'.'+QUOTENAME([REFERENCED_TABLE_NAME]) + ' ( '+[REFERENCED_COLUMNS] + ' ) ' AS SQLCMD
FROM (
SELECT DISTINCT TC.CONSTRAINT_NAME,TC.TABLE_SCHEMA,TC.TABLE_NAME [TABLE_NAME],TC2.TABLE_SCHEMA AS [REFERENCED_TABLE_SCHEMA],TC2.TABLE_NAME [REFERENCED_TABLE_NAME]
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COLUMN_NAME) AS 'data()' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE IC
WHERE IC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME ORDER BY ORDINAL_POSITION
FOR XML PATH('')),'~~',', '), 1, 2, '') as [COLUMNS]
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COLUMN_NAME) AS 'data()' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE IC
WHERE IC.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME ORDER BY ORDINAL_POSITION
FOR XML PATH('')),'~~',', '), 1, 2, '') as [REFERENCED_COLUMNS]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON TC.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON RC.UNIQUE_CONSTRAINT_SCHEMA = TC2.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = TC2.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON TC2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA AND TC2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE TC.CONSTRAINT_TYPE ='FOREIGN KEY'
) A
'IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_NAME = ''' +CONSTRAINT_NAME +''') ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME) + ' DROP CONSTRAINT ' +QUOTENAME(CONSTRAINT_NAME)+' ALTER TABLE '+QUOTENAME(TABLE_SCHEMA)+'.'+QUOTENAME(TABLE_NAME)+' WITH CHECK ADD CONSTRAINT '+QUOTENAME(CONSTRAINT_NAME)
+' FOREIGN KEY('+[COLUMNS]+' ) REFERENCES '
+ QUOTENAME([REFERENCED_TABLE_SCHEMA])+'.'+QUOTENAME([REFERENCED_TABLE_NAME]) + ' ( '+[REFERENCED_COLUMNS] + ' ) ' AS SQLCMD
FROM (
SELECT DISTINCT TC.CONSTRAINT_NAME,TC.TABLE_SCHEMA,TC.TABLE_NAME [TABLE_NAME],TC2.TABLE_SCHEMA AS [REFERENCED_TABLE_SCHEMA],TC2.TABLE_NAME [REFERENCED_TABLE_NAME]
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COLUMN_NAME) AS 'data()' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE IC
WHERE IC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME ORDER BY ORDINAL_POSITION
FOR XML PATH('')),'~~',', '), 1, 2, '') as [COLUMNS]
,STUFF(REPLACE((SELECT '~~' + QUOTENAME(COLUMN_NAME) AS 'data()' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE IC
WHERE IC.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME ORDER BY ORDINAL_POSITION
FOR XML PATH('')),'~~',', '), 1, 2, '') as [REFERENCED_COLUMNS]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON TC.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON TC.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA AND TC.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC2 ON RC.UNIQUE_CONSTRAINT_SCHEMA = TC2.CONSTRAINT_SCHEMA AND RC.UNIQUE_CONSTRAINT_NAME = TC2.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON TC2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA AND TC2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE TC.CONSTRAINT_TYPE ='FOREIGN KEY'
) A
Thank you,
Narasimha
No comments:
Post a Comment