Monday, November 9, 2009

Delete all foreign keys in a database


DECLARE @Table VARCHAR(255)
DECLARE @ForeignKey VARCHAR(255)
DECLARE @STRSQL VARCHAR(8000)

DECLARE Cur CURSOR FOR
select t1.name as [Table], t2.name as [ForeignKey]
from sysobjects t1 inner join
sysobjects t2 on t1.id = t2.parent_obj
where t1.xtype = 'U' and t2.xtype = 'F'
OPEN Cur
FETCH FROM Cur
INTO @Table, @ForeignKey
WHILE @@FETCH_STATUS = 0
BEGIN
SET @STRSQL = 'ALTER TABLE ' + @Table +
' DROP CONSTRAINT ' + @ForeignKey
EXECUTE(@STRSQL)

FETCH FROM Cur
INTO @Table, @ForeignKey
END
CLOSE Cur
DEALLOCATE Cur

No comments:

Post a Comment