Find broken objects in SQL Server

sql-server, sql-server-2008

Solution

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

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
            }
        }
    }
}

Problem

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?

Original source