Script to Drop Constraints from Linked Tables Before Drop Table


DECLARE @sql varchar(max)=''
SELECT @sql = @sql + 'ALTER TABLE ' +  Quotename(OBJECT_SCHEMA_NAME(parent_object_id)) +'.' + Quotename(OBJECT_NAME(parent_object_id) )+ 
' DROP CONSTRAINT ' + name + ';'+ CHAR(13)
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('myscm.thetable' )
or referenced_object_id = object_id( 'myscm.anothertable')

SELECT @sql = @sql+ CHAR(13)+ ' DROP TABLE ' '.'+  
FROM sys.Tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
where'myscm' and in ('thetable', 'anothertable')

-----uncomment below line to execute the generated script
 --EXEC sp_executesql @sql 

PRINT @sql 



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s