主页 / dba / 问题 / 289491
Accepted
Carl Patenaude Poulin
Asked: 2021-04-09 06:40:20 +0800 CST

获取 SQL Server 数据库的“切片”：对于所有表，仅获取连接（直接或间接）特定行的行

  • 772

出于审计目的，我正在尝试获取数据库的副本/导出，但仅包括每个表中与某个表中的一小部分行相关的行。所以这些行指向的每个表的每一行；每个表中指向这些行的每一行；等等。（在代数术语中，这是这些行相对于外键关系的传递对称自反闭包。）

我想知道是否存在某种工具可以做到这一点，或者这是否可以以某种方式编写脚本。我有大约 400 个表，外键网络很紧密，所以我不想手动执行此操作。

sql-server backup

1 个回答

  1. Best Answer

    您当然可以使用嵌套存储过程编写脚本：

    1. 被称为类似EXEC recursive_shaped_query @StartTable='SomeTable', @KeyColumn='Id', @ValuesSQL='SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id)'
    2. 然后通过 ad-hoc SQL 运行SELECT * FROM SomeTable WHERE Id IN (SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id))
    3. 然后它会通过光标sys.foreign_keys找到任何引用该表的表并...
    4. …对于每个找到的此类表，都称自己为EXEC recursive_shaped_query @StartTable='ReferencingTable', @KeyColumn='Id', @ValuesSQL='SELECT Id FROM SomeTable WHERE Id IN (SELECT Id FROM (VALUES (1),(6),(4576),(42)) AS Ids(Id))'

    当然，会有一些复杂的情况需要考虑：

    • 如果您的数据结构允许循环链接，例如包含状态机图的表格，或者两个实体可能相互引用的位置，您将需要决定该怎么做，否则这将循环到无穷大（好吧，直到您点击“过程调用嵌套太深”错误）。
    • 您可能需要处理作用于多个字段而不仅仅是一个字段的外键。
    • 如果有用的索引没有到位，SELECT * FROM t1 WHERE Id IN (SELECT Id FROM t2 WHERE Id IN (SELECT Id FROM Tt3 WHERE ... )))嵌套可能会变得很长，并且是由于表扫描而需要很长时间才能运行的查询。

    其他需要注意的事项：

    • 您可能会发现使用连接而不是子选择会产生更高效的查询（尽管我认为查询规划器会在优化这两个语法选项以使其等效方面做得不错）。
    • 您可能更喜欢该过程只是发出所需的临时 SQL 而不是运行它，因此您可以稍后运行它（并在其他地方再次运行它）。
    • 如果只是发出 ad-hoc SQL 而不运行它，则在 sys.foreign_keys 上的递归 CTE 可能比嵌套存储过程明显更干净 - 实际上这样做可能会更好：在运行中生成 SQL 然后在一个中运行它去，也许显式地将数据删除到临时表，然后结束当前事务以释放锁，然后再将数据推送给读取器。
    • 这当然是假设您在应该在的任何地方都定义了外键。在太多的数据库中，缺少部分或全部这些约束。

    对于我们在 [Day Job] 中的系统，我手动创建了一组这样的查询，以提取给定实体的嵌套数据，因为这些过程对诊断很有用，但我从来没有需要足够多的查询来保证尝试创建生成器或其他自动化解决方案。我一直认为它是一个游戏项目，但实际上并没有这样做。

    • 1

相关问题