Re: Turning Off Foreign Keys during DB coping

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

You can create a task that will create a script (or even a Stored Proc) that
will disable and then reenable the constraints. You can generate both
scripts by the below resultset:

SELECT 'ALTER TABLE ' + TABLE_NAME + ' NOCHECK CONSTRAINT ALL' FROM
INFORMATION_SCHEMA.TABLES

SELECT 'ALTER TABLE ' + TABLE_NAME + ' CHECK CONSTRAINT ALL' FROM
INFORMATION_SCHEMA.TABLES

Scott

"Shimon Sim" <shimonsim048@xxxxxxxxxxxxxxxx> wrote in message
news:e$ma4qu6FHA.3752@xxxxxxxxxxxxxxxxxxxxxxx
> Michael
> Do you suggest me to write the script for each table to disable keys?
> It is not really a solution since I have over 100 tables. Plus the schema
> changes and my script can become invalid.
> Is there something that can disable all the keys whatever they are?
> An if such thing exists how can I out it in the DTS package together with
> backup and data transfer tasks?
>
> Thank you,
> Shimon
>
> "Michael Cheng [MSFT]" <v-mingqc@xxxxxxxxxxxxxxxxxxxx> wrote in message
> news:%2338TAel6FHA.3036@xxxxxxxxxxxxxxxxxxxxxxxx
>> Hi Shimon,
>>
>> Welcome to use MSDN Managed Newsgroup!
>>
>> You could use NOCHECK instead of UNCHECKED when altering a existing
>> constraint to be invalid. I made a sample on my side with DTS and it
>> works.
>> Here is a sample
>>
>> CREATE TABLE Test2
>> (
>> TID INT Primary KEY,
>> CID INT IDENTITY(1,1) UNIQUE
>> )
>>
>> INSERT Test2 VALUES (11)
>> INSERT Test2 VALUES (12)
>> INSERT Test2 VALUES (13)
>> INSERT Test2 VALUES (14)
>>
>> --SELECT * FROM Test2
>>
>> CREATE TABLE Test2Child
>> (
>> TCID INT FOREIGN KEY REFERENCES Test2(CID),
>> TC CHAR(1)
>> )
>>
>> INSERT Test2Child VALUES (55,'B')
>> --The statement above will fail
>> ALTER TABLE Test2Child NOCHECK CONSTRAINT FK__Test2Child__TCID__48CFD27E
>>
>> INSERT Test2Child VALUES (55,'B')
>> --The statement above will do since we have altered NOCHECK
>> --Use a DTS package to do the test, it also works
>>
>> NOTE that if you have specified NOCHECK for the constraint, it will not
>> check the constraints any more.
>>
>>
>>
>> Sincerely yours,
>>
>> Michael Cheng
>> Microsoft Online Partner Support
>>
>> When responding to posts, please "Reply to Group" via your newsreader so
>> that others may learn and benefit from your issue.
>> =====================================================
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>
>


.

Relevant Pages

  • Re: Turning Off Foreign Keys during DB coping
    ... Do you suggest me to write the script for each table to disable keys? ... > CREATE TABLE Test2 ...
    (microsoft.public.sqlserver.dts)
  • Re: Turning Off Foreign Keys during DB coping
    ... > You can create a task that will create a script ... > that will disable and then reenable the constraints. ... >> Do you suggest me to write the script for each table to disable keys? ... >>> CREATE TABLE Test2 ...
    (microsoft.public.sqlserver.dts)
  • Re: How to GET The Name of the Default?
    ... > It's better to give meaningful names to defaults, keys and constraints ... > CREATE TABLE emp ... > If you script the table from Query Analyzer's Object Browser you will see ...
    (microsoft.public.sqlserver.programming)
  • Re: ALTER TABLE: drop all objects and recreate them.
    ... To script only the keys, defaults & constraints for all the table ... Click OK or use the Preview button in the General Tab ...
    (microsoft.public.sqlserver.programming)
  • Re: Add columns to table, but in an order
    ... in the order that SQL Server mentions them, with no way for the end user ... existing constraints and recreate them when the new table is in place. ... be very critical of the script ... Make sure to test the conversion on a test server first. ...
    (microsoft.public.sqlserver.server)