SQL Server的截断表 - 删除并重建FK约束脚本 [英] SQL Server truncate table - drop and recreate FK constraints script

问题描述

我写的小应用程序（在C＃），这有助于我截断在SQL Server 2005/08表。为了截断表，我想我需要做的：

I'm writing small application (in c#) which helps me to truncate tables in SQL Server 2005/08. In order to truncate table I think I need to do this:

• 丢弃所有的FK约束表，
• 截断表，
• 重建所有previously删除约束。

有人可以帮助我创造这样一个脚本，或点我在哪里可以找到一些线索？

Can someone help me to create such a script, or point me where I can find some clues?

问候

推荐答案

那么，你可以从你的应用程序做到这一点：

Well, you could do this from your application:

• 在现有的数据库中运行SQL命令来查找所有的外键约束
• 从外键约束的列表，创建两个脚本
  • 一个删除所有现有的外键约束（您截断表之前）
  • 第二个重新创建外键约束你截断表后
  • run a SQL command on your existing database to find all foreign key constraints
  • from that list of foreign key constraints, create two scripts
    • one to drop all existing foreign key constraints (before you truncate the tables)
    • a second one to re-create the foreign key constraints after you've truncated the tables

    您可以通过检查系统目录视图做到这一点。

    You can do this by inspecting the system catalog view.

    下面这个查询将给你的所有外键约束列表：

    This query here will give you a list of all foreign key constraints:

    select
        fk.name,
        object_name(fk.parent_object_id) 'Parent table',
        c1.name 'Parent column',
        object_name(fk.referenced_object_id) 'Referenced table',
        c2.name 'Referenced column'
    from 
        sys.foreign_keys fk
    inner join
        sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    inner join
        sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
    inner join
        sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
    

    通过结合这些元素，你可以创建 DROP CONSTRAINT 的命令列表的表的截断之前运行：

    By combining these elements, you can create the list of DROP CONSTRAINT commands to be run before the truncation of the tables:

    select
        'ALTER TABLE dbo.' + object_name(fk.parent_object_id) + 
        ' DROP CONSTRAINT ' + fk.name
    from 
        sys.foreign_keys fk
    

    ，你还可以创建要运行的 ALTER TABLE 脚本截断恢复外键关系后。

    and you can also create the ALTER TABLE scripts to be run after the truncating to restore the foreign key relationships.

    select
        'ALTER TABLE dbo.' + object_name(fk.parent_object_id) + 
        ' ADD CONSTRAINT ' + fk.name +
        ' FOREIGN KEY(' + c1.name + ') REFERENCES dbo.' + 
        object_name(fk.referenced_object_id) + '(' + c2.name + ')'
    from 
        sys.foreign_keys fk
    inner join
        sys.foreign_key_columns fkc ON fk.object_id = fkc.constraint_object_id
    inner join
        sys.columns c1 ON fkc.parent_column_id = c1.column_id and c1.object_id = fkc.parent_object_id
    inner join
        sys.columns c2 ON fkc.referenced_column_id = c2.column_id and c2.object_id = fkc.referenced_object_id
    

    有关这两个查询，这是一个两步过程：

    For these two queries, it's a two-step process:

    • 先执行，我显示了使用C＃和ADO.NET对数据库中的查询
    • 这将产生这是T-SQL命令的列表（删除或重新创建FK关系）
    输出
    • 乘坐输出，并在第二个步骤，执行作为T-SQL命令批处理从C＃/ ADO.NET应用程序的输出。

    限制：现在，该脚本假设，并只有当你有单列外键;如果你没有，你可能需要调整脚本一点。

    Limitation: right now, the script assumes and works only if you have single-column foreign keys; if you don't have that, you might need to tweak the scripts a bit.

    这篇关于SQL Server的截断表 - 删除并重建FK约束脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！