用于更改所有外键以添加 ON DELETE CASCADE 的 SQL 脚本

Question

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

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

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

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

然后我运行该脚本，进行删除，然后运行另一个脚本。

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

是否有替代选项可以在系统表上运行选择来为我“生成”脚本？甚至可以在系统表上运行更新来启用和禁用 ON DELETE CASCADE 吗？

Answer 1

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

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'))