Blog Post

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


 

 

Read 4,450 times
(26 in last 30 days)

Rate

You rated this post out of 5. Change rating

Share

Share

Rate

You rated this post out of 5. Change rating