Post reply

Disable RI in a database

  • April 7, 2005 at 3:28 am

    #66178

    Hi,

    Does anyone have a script that enables/disables all FK constraints in a database? I've done a skim of Google and this site but can't find anything.

    Better still, is there a SQL Server command for doing this? If there isn't, there should be!

    Background to this is that we have a new version of a table to deploy. The table is referenced in many places so we have to go through and manually turn off all the FK constraints - very time consuming! We are going to write a script to do this but don't want to reinvent the wheel - so I'm wondering if somebody has already done this for us.

    Thanks

    Jamie

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • April 7, 2005 at 5:38 am

    #551099

    I think this may do it ...

    EXEC sp_MSforeachtable @command1= 'alter table ? nocheck constraint all'

    and to get them back

    EXEC sp_MSforeachtable @command1= 'alter table ? check constraint all'

    of course this would do it a table at a time from within a database but you could wrap this in sp_MSforeachdatabase ...

    cheers

    dbgeezer

  • April 7, 2005 at 5:50 am

    #551101

    I never knew about that sproc. Fantastic.

    Thanks geezer!

     

    Jamie Thomson
    http://sqlblog.com/blogs/jamie_thomson

  • April 7, 2005 at 9:31 am

    #551206

    Just watch out for deletes though

     


    * Noel

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply