在 SQL Server 中查找损坏的对象 [英] Find broken objects in SQL Server

问题描述

是否有一种工具可以找到 SQL Server 中所有由于引用不存在的对象而无法工作的对象(函数、过程、视图)?

Is there a tool that will find all objects in SQL Server (functions, procs, views) that cannot possibly work because they refer to objects that don't exist?

推荐答案

我实际上正在使用 sys.refreshmodule 过程，现在包装在带有 SQL Server Powershell 插件的 powershell 脚本中.

I'm actually using sys.refreshmodule procedure now wrapped in a powershell script with the SQL Server Powershell add ins.

这效果更好，因为这个方便的小 sys 函数摆脱了 CREATE 与 ALTER 的东西.此处的其他一些答案也使用这种方法，但我更喜欢包含在 Powershell 中的这种方法，也许有些人会发现它很有用.

This works better because this handy little sys function gets rid of the CREATE vs ALTER stuff. Some other answers here use this approach as well, but I prefer this one that's wrapped in Powershell and maybe some will find it useful.

$server = "YourDBServer"
cls
Import-Module "sqlps" -DisableNameChecking

$databases = Invoke-Sqlcmd -Query "select name from sys.databases where name not in ('master', 'tempdb', 'model', 'msdb')" -ServerInstance $server
foreach ($db in $databases) {
    $dbName = $db.name
    $procedures = Invoke-Sqlcmd -Query "select SCHEMA_NAME(schema_id) as [schema], name from $dbName.sys.procedures" -ServerInstance $server
    foreach ($proc in $procedures) {
        if ($schema) {
            $shortName = $proc.schema + "." + $proc.name
            $procName =  $db.name + "." + $shortName
            try {
                $result = Invoke-Sqlcmd -Database $dbName -Query "sys.sp_refreshsqlmodule '$shortName'" -ServerInstance $server -ErrorAction Stop
                Write-Host "SUCCESS|$procName"
            }
            catch {
                $msg = $_.Exception.Message.Replace([Environment]::NewLine, ",")
                Write-Host "FAILED|$procName|$msg" -ForegroundColor Yellow
            }
        }
    }
}

这篇关于在 SQL Server 中查找损坏的对象的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！