用于更改所有外键以添加 ON DELETE CASCADE 的 SQL 脚本 [英] SQL Script to alter ALL Foreign Keys to add ON DELETE CASCADE

问题描述

我有一个包含大约 250 个表的 SQL 2005 数据库.

I have a SQL 2005 database with approx 250 tables.

我想对所有外键临时启用 ON DELETE CASCADE，以便我可以轻松地进行批量删除.

I want to temporarily enable ON DELETE CASCADE to all of the Foreign Keys so that I can do a bulk delete easily.

然后我想关闭所有外键的 ON DELETE CASCADE.

I then want to turn off ON DELETE CASCADE on all Foreign Keys.

我知道这样做的唯一方法是使用 Management Studio 生成完整的数据库创建脚本，进行某种搜索和替换以去除除外键之外的所有内容，保存脚本，然后进行更多搜索和替换以添加 ON DELETE CASCADE.

The only way I know of doing this, is to use Management Studio to generate a full database create script, do some kind of search and replace to strip out everything but Foreign Keys, save the script, then do some more search and replacing to add the ON DELETE CASCADE.

然后我运行脚本，执行我的删除操作，然后运行另一个脚本.

Then I run the script, do my delete, and then run the other script.

有没有更简单的方法来制作这个脚本?这种方法似乎太容易出错了，我必须使脚本与我们对数据库所做的任何其他更改保持同步，或者每次我可能需要使用它时都手动重新生成它.

Is there an easier way to produce this script? This method seems far too prone to error and I will have to keep the script up to date with any other changes we make to the database, or re-generate it manually each time I may need to use it.

在系统表上运行选择来为我生成"脚本的替代选项是什么?甚至可以在启用和禁用 ON DELETE CASCADE 的系统表上运行更新吗?

Is an alternative option to run a select on the system tables to "generate" the script for me? Could it even be possible to run an update on a system table that enables and disables ON DELETE CASCADE?

推荐答案

这是我用于类似目的的脚本.它不支持复合外键(使用多个字段).它可能需要一些调整才能适合您的情况.特别是它不能正确处理多列外键.

Here's a script I used for a similiar purpose. It does not support composite foreign keys (which use more than one field.) And it would probably need some tweaking before it will work for your situation. In particular it does not handle multi-column foreign keys correctly.

select
  DropStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] DROP CONSTRAINT [' + ForeignKeys.ForeignKeyName + ']; '
,  CreateStmt = 'ALTER TABLE [' + ForeignKeys.ForeignTableSchema + 
      '].[' + ForeignKeys.ForeignTableName + 
      '] WITH CHECK ADD CONSTRAINT [' +  ForeignKeys.ForeignKeyName + 
      '] FOREIGN KEY([' + ForeignKeys.ForeignTableColumn + 
      ']) REFERENCES [' + schema_name(sys.objects.schema_id) + '].[' +
  sys.objects.[name] + ']([' +
  sys.columns.[name] + ']) ON DELETE CASCADE; '
 from sys.objects
  inner join sys.columns
    on (sys.columns.[object_id] = sys.objects.[object_id])
  inner join (
    select sys.foreign_keys.[name] as ForeignKeyName
     ,schema_name(sys.objects.schema_id) as ForeignTableSchema
     ,sys.objects.[name] as ForeignTableName
     ,sys.columns.[name]  as ForeignTableColumn
     ,sys.foreign_keys.referenced_object_id as referenced_object_id
     ,sys.foreign_key_columns.referenced_column_id as referenced_column_id
     from sys.foreign_keys
      inner join sys.foreign_key_columns
        on (sys.foreign_key_columns.constraint_object_id
          = sys.foreign_keys.[object_id])
      inner join sys.objects
        on (sys.objects.[object_id]
          = sys.foreign_keys.parent_object_id)
        inner join sys.columns
          on (sys.columns.[object_id]
            = sys.objects.[object_id])
           and (sys.columns.column_id
            = sys.foreign_key_columns.parent_column_id)
    ) ForeignKeys
    on (ForeignKeys.referenced_object_id = sys.objects.[object_id])
     and (ForeignKeys.referenced_column_id = sys.columns.column_id)
 where (sys.objects.[type] = 'U')
  and (sys.objects.[name] not in ('sysdiagrams'))

这篇关于用于更改所有外键以添加 ON DELETE CASCADE 的 SQL 脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！