Razvan Socol
3/19/2007 1:14:00 PM
Hello, Roy
If you are concerned only about foreign key constraints (so that you
can insert rows in an empty database, with an arbitrary order of
tables), you can disable the constraints and re-enable them after all
inserts are done, by using something like this:
DECLARE tables CURSOR LOCAL FOR
SELECT name AS TableName, USER_NAME(uid) AS Owner
FROM sysobjects WHERE xtype='U' AND OBJECTPROPERTY(id,'IsMSShipped')=0
DECLARE @TableName sysname, @Owner sysname, @SQL nvarchar(4000)
OPEN tables
WHILE 1=1 BEGIN
FETCH NEXT FROM tables INTO @TableName, @Owner
IF @@FETCH_STATUS<>0 BREAK
SET @SQL='ALTER TABLE '
+QUOTENAME(@Owner)+'.'+QUOTENAME(@TableName)
+'NOCHECK CONSTRAINT ALL'
EXEC (@SQL)
END
CLOSE tables
DEALLOCATE tables
[... insert data in all tables ...]
DECLARE tables CURSOR LOCAL FOR
SELECT name AS TableName, USER_NAME(uid) AS Owner
FROM sysobjects WHERE xtype='U' AND OBJECTPROPERTY(id,'IsMSShipped')=0
DECLARE @TableName sysname, @Owner sysname, @SQL nvarchar(4000)
OPEN tables
WHILE 1=1 BEGIN
FETCH NEXT FROM tables INTO @TableName, @Owner
IF @@FETCH_STATUS<>0 BREAK
SET @SQL='ALTER TABLE '
+QUOTENAME(@Owner)+'.'+QUOTENAME(@TableName)
+'WITH CHECK CHECK CONSTRAINT ALL'
EXEC (@SQL)
END
CLOSE tables
DEALLOCATE tables
This will enable all constraints, even if they were disabled in the
first place. So if you want to keep some constraints disabled, you
should change the script accordingly.
Razvan