Post reply

Alter all FK constraints removing update cascade.

  • June 26, 2006 at 8:21 am

    #114145

    I need to put 'instead of' triggers on a large number of tables but cant because the FK s on the tables were created with the update cascade option on.

    Does anyone know how i can create a script that will alter all the FKs in the DB.

    I have looked in sql.modules and syscomments for the definition SQL inorder to dynamically create alter scripts but the definition does not appear to be stored in these tables and I am using 2005 so i cant update sys tables....

    Also the 'Instead of' triggers are only for update so the delete cascade option can remain in place.

    Any pointers would be greatly appreciated.

     

    Thanks,

    Jules

    www.sql-library.com[/url]

  • June 26, 2006 at 8:53 am

    #645852

    This code will give the list of child tables and master tables.

    SELECT object_name(fkeyid) AS child

     ,object_name(constid)AS FK

     ,object_name(rkeyid) AS master

     FROM sysreferences

    WHERE object_name(fkeyid) IN ('<TABLE_NAME>,<TABLE_NAME>,<TABLE_NAME>,<TABLE_NAME>,...')

     OR object_name(rkeyid) IN ('<TABLE_NAME>,<TABLE_NAME>,<TABLE_NAME>,<TABLE_NAME>,...')

    ORDER BY 1

    ------------
    Prakash Sawant

    http://psawant.blogspot.com

  • June 26, 2006 at 9:04 am

    #645856

    For v9+, sys.key_constraints, sys.indexes, sys.idex_columns, sys.foreign_keys, sys.foreign_key_columns and object_name() should give a most of what you need.

    Tim Wilkinson

    "If it doesn't work in practice, you're using the wrong theory"
    - Immanuel Kant

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

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