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
Monday, November 9, 2009
Delete all foreign keys in a database
at 6:15 AM
Labels: SQL Server, SQL Server 2000, SQL Server 2005, SQL Server Tip
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment