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


 

 

Rate

Share

Share

Rate