Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Remove All Foreign Keys

To clear test data from a database we could use truncate commands but sometimes we need to get rid of foreign keys.

we could get constaint list from INFORMATION_SCHEMA.TABLE_CONSTRAINTS table.

for example :

Constaint List

we could remove foreign key by using this table and the following query.

while(exists(select 1 from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE='FOREIGN KEY'))
begin
	declare @sql nvarchar(2000)
	SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
	+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
	FROM information_schema.table_constraints
	WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
	exec (@sql)
end


 

 

Comments

Posted by Anonymous on 12 October 2011

Pingback from  Dew Drop – October 12, 2011 | Alvin Ashcraft's Morning Dew

Posted by Yitzchok Lavi on 23 October 2011

Surely as you remove them you would want to save the removed constraints so that you could restore them?

See stackoverflow.com/.../can-foreign-key-constraints-be-temporarily-disabled-using-t-sql where this is discussed.

I personally would prefer to disable the constraints and re-enable them rather than dropping and recreating them (disabling the constraint would still prevent use of TRUNCATE TABLE - you'd have to use DELETE, but the constraints wouldn't be enforced)

Leave a Comment

Please register or log in to leave a comment.