PowerShell SMO DependencyWalker无法识别ALTER TABLE类表依赖的问题

问题描述 #

我正在用PowerShell结合SMO的DependencyWalker和Scripter工具，查找SQL Server数据库中表的所有依赖项，用来生成创建、删除表及其依赖的脚本。DependencyWalker能识别大部分依赖，但无法找到仅通过ALTER TABLE语句引用目标表的存储过程。

示例场景 #

表定义：

CREATE TABLE MyTable(
   [UniqueID] [uniqueidentifier] NOT NULL
)

CREATE TABLE MyOtherTable(
   [UniqueID] [uniqueidentifier] NOT NULL
)

ALTER TABLE [dbo].[MyTable]
WITH CHECK ADD CONSTRAINT [FK_MyTable_MyOtherTable] FOREIGN KEY([UniqueID])
REFERENCES [dbo].[MyOtherTable] ([UniqueID])

相关存储过程：

CREATE PROCEDURE [dbo].[MyTableResumeConstraintChecking]
AS
    ALTER TABLE dbo.MyTable CHECK CONSTRAINT FK_MyTable_MyOtherTable
    RETURN
GO
CREATE PROCEDURE [dbo].[MyTableSuspendConstraintChecking]
AS
    ALTER TABLE dbo.MyTable NOCHECK CONSTRAINT FK_MyTable_MyOtherTable
    RETURN
GO

我执行以下PowerShell脚本期望找到这些存储过程作为依赖：

$SmoServer = New-Object ('Microsoft.SqlServer.Management.SMO.Server') $server
$dbObject = $SmoServer.Databases["MyDatabase"]
$tables = $dbObject.Tables | where { !$_.IsSystemObject }
$urns = $tables | foreach { $_.Urn }

$walker = New-Object 'Microsoft.SqlServer.Management.SMO.DependencyWalker' $SmoServer
$tree = $walker.DiscoverDependencies($urns, $false)
$depCollection = $walker.WalkDependencies($tree)

$dependencies = $depCollection | foreach { $_.Urn }

但该脚本只能找到用INSERT、UPDATE等DML操作引用MyTable的存储过程，无法识别上述*ConstraintChecking系列存储过程。SSMS的「查看依赖项」、sp_depends、sys.dm_sql_referenced_entities也都无法识别这类依赖。

请问：在PowerShell中使用SMO，给定表的URN后，如何找到这类依赖项的URN？

解决方案 #

SQL Server默认的依赖追踪机制（包括SMO DependencyWalker、系统视图和函数）不会记录DDL语句（比如ALTER TABLE）中的对象引用，这类依赖需要通过解析对象的定义文本来识别。以下是实现步骤：

1. 获取目标表的完整标识 #

从SMO Table对象中提取架构和表名，准备两种格式匹配不同写法：

$targetTable = $dbObject.Tables["MyTable", "dbo"]
# 带方括号的格式
$targetFullName = "[$($targetTable.Schema)].[$($targetTable.Name)]"
# 不带方括号的格式
$targetFullNameNoBrackets = "$($targetTable.Schema).$($targetTable.Name)"

2. 遍历可编程对象，解析定义文本 #

遍历数据库中的存储过程、函数、触发器等对象，检查其定义是否包含目标表的标识：

$ddlDependentUrns = @()

# 遍历存储过程
foreach ($proc in $dbObject.StoredProcedures | Where-Object { !$_.IsSystemObject }) {
    $procDefinition = $proc.TextBody
    # 匹配两种表名格式
    if ($procDefinition -match [regex]::Escape($targetFullName) -or $procDefinition -match [regex]::Escape($targetFullNameNoBrackets)) {
        $ddlDependentUrns += $proc.Urn
    }
}

# 可选：遍历用户定义函数
foreach ($func in $dbObject.UserDefinedFunctions | Where-Object { !$_.IsSystemObject }) {
    $funcDefinition = $func.TextBody
    if ($funcDefinition -match [regex]::Escape($targetFullName) -or $funcDefinition -match [regex]::Escape($targetFullNameNoBrackets)) {
        $ddlDependentUrns += $func.Urn
    }
}

# 可选：遍历触发器
foreach ($trigger in $dbObject.Triggers | Where-Object { !$_.IsSystemObject }) {
    $triggerDefinition = $trigger.TextBody
    if ($triggerDefinition -match [regex]::Escape($targetFullName) -or $triggerDefinition -match [regex]::Escape($targetFullNameNoBrackets)) {
        $ddlDependentUrns += $trigger.Urn
    }
}

3. 合并所有依赖 #

将DependencyWalker找到的默认依赖和手动解析的DDL依赖合并去重：

$defaultDependentUrns = $depCollection | ForEach-Object { $_.Urn }
$allDependentUrns = $defaultDependentUrns + $ddlDependentUrns | Select-Object -Unique

注意事项 #

• 避免误匹配：如果对象定义中存在字符串常量包含表名（比如PRINT '正在处理MyTable'），会被误判为依赖。可以优化正则表达式，限定匹配ALTER TABLE关键字后的表名：

  $regexPattern = "ALTER\s+TABLE\s+(?:\[\w+\]\.\[\w+\]|\w+\.\w+|\[\w+\]|\w+)"
  $matches = [regex]::Matches($procDefinition, $regexPattern, [System.Text.RegularExpressions.RegexOptions]::IgnoreCase)
  foreach ($match in $matches) {
      if ($match.Value -like "*$($targetTable.Name)*") {
          $ddlDependentUrns += $proc.Urn
          break
      }
  }
  

• 大小写敏感：如果数据库排序规则区分大小写，需调整正则表达式的匹配选项。
• 性能优化：大型数据库中，可过滤对象类型或添加索引来提升解析速度。

内容的提问来源于stack exchange，提问作者Dean Johnson