Monday 30 September 2019

How to get DROP AND CREATE script for all FOREIGN KEY constraints USING simple SELECT in SQL SERVER

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


Thank you,
Narasimha 

No comments:

Post a Comment