SQL Server Performance

Import-FK problem

Discussion in 'SQL Server DTS-Related Questions' started by Reddy, Aug 14, 2007.

  1. Reddy New Member

    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
  2. ndinakar Member

    If its going to be a regular thing, perhaps you can script out the constraints, drop them, do the import and recreate them?
    You can disable a constraint but not sure if you can disable FK's. Check in BOL under Constraints -> Enabling.
  3. Reddy New Member

    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

Share This Page