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 ' +s.name+ '.'+ t.name  
FROM sys.Tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
where s.name='myscm' and  t.name in ('thetable', 'anothertable')


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

PRINT @sql 

 
 

 
Advertisements


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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