DROP and CREATE All Constraints

Notes

DROP and CERATE All ViewsThis blog has become a notebook where I can write down queries that I will reuse later on.

Have you ever wanted to DROP and CREATE all constraints in your database?

…oh, of course you have.

Is the definition of cringe; can you picture yourself pressing F5 on the button while the whole database is being modified forever? And what if it modify only half of it?

So put your belt on as with great pleasure come great fear and this might be like a car crash you can see but you just can’t avoid.

(…when I say “put your belt” I mean back up your database)

Press the button

Here we are with the query that will possibly change your day or your LinkedIn profile:

SELECT cs.name AS SchemaName
	,ct.name AS TableName
	,rt.name AS ColumnName
	,fk.name AS ForeignKeyName
	,fk.object_id AS ObjectID
	,fk.parent_object_id AS ParentObjectID
	,
	-- drop constraint 
	N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';' AS Drop_Constraint_Script
	,
	-- create constraint 
	N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) + ' FOREIGN KEY (' + STUFF((
			SELECT ',' + QUOTENAME(c.name)
			-- get all the columns in the constraint table
			FROM sys.columns AS c
			INNER JOIN sys.foreign_key_columns AS fkc ON fkc.parent_column_id = c.column_id
				AND fkc.parent_object_id = c.[object_id]
			WHERE fkc.constraint_object_id = fk.[object_id]
			ORDER BY fkc.constraint_column_id
			FOR XML PATH(N'')
				,TYPE
			).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name) + '(' + STUFF((
			SELECT ',' + QUOTENAME(c.name)
			-- get all the referenced columns
			FROM sys.columns AS c
			INNER JOIN sys.foreign_key_columns AS fkc ON fkc.referenced_column_id = c.column_id
				AND fkc.referenced_object_id = c.[object_id]
			WHERE fkc.constraint_object_id = fk.[object_id]
			ORDER BY fkc.constraint_column_id
			FOR XML PATH(N'')
				,TYPE
			).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');' AS Create_Constraint_Script
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
	ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
	ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0
	AND ct.is_ms_shipped = 0
ORDER BY 1
	,2
	,3

Two column will be created: Drop_Constraint_Script and Create_Constraint_Script .

I tested it on AdventureWorks2019 and it made the job done™.

Will it do the same on your database? Who knows…

 

Related

Leave a Reply

You have to agree to the comment policy.