August 14, 2007 at 10:33 am
I am trying to import Prod data to Dev by using DTS wizard, but it gets failed as there are many FK relationships.
IS there a way to disable FK's-do my import - Enable FK's.
or if anyone has any script to disable FK and enable them later, that wud really work gr8 for me.
thanks
August 14, 2007 at 10:45 am
Hi,
Did you try the script I posted the last time you asked about foreign keys? It produces statements to drop and recreate the constraints.
Greg
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=387089
Greg
August 14, 2007 at 11:16 am
You can also browse BOL in the alter table ection to find out what script can disable/enable a constraint (any type).
August 14, 2007 at 11:28 am
thank you guys... I got it worked out by runnig this script which was written by vyas
/*To disable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' NOCHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To disable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To disable constraints on tables starting with a particular pattern*/
GO
/*To enable constraints*/
SELECT 'ALTER TABLE ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' CHECK CONSTRAINT ALL'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
--AND TABLE_SCHEMA = 'Admin1' /*To enable constraints on tables owned by 'Admin1' only*/
--AND TABLE_NAME LIKE 'Tbl%' /*To enable constraints on tables starting with a particular pattern*/
GO
August 14, 2007 at 11:32 am
You realize that ALL constraints are disabled by this script. You better make sure to revalidate all your data before inserting it into the table.
Also doing that will mark the constraints as unstrusted (can have some affect on performance). You can run this script to recheck the constraint validity : DBCC CHECKCONSTRAINTS.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply