Finding SQL Server objects that reference invalid objects

By: Tim Smith   |   Comments (2)   |   Related: > Stored Procedures

Problem

We haven't committed to using source control and we have SQL Server objects in production that reference objects that have changed and are therefore no longer valid. With hundreds of servers, and hundreds of objects per server, is there a way to minimize the headache of identifying these mismatched objects?

Solution

Before we begin, I would highly suggest source control for SQL Server and its objects. Unfortunately, some developers think databases don't need source control, and this can be a costly mistake. Since many environments haven't committed to using a source control system, identifying mismatched objects can offer a challenge, especially if there's enough of them.

The below script demonstrates an example of a stored procedure, which refers to a table that is later changed, causing the stored procedure to be invalid. Note that this can also happen with other objects such as views, triggers, etc.

CREATE TABLE ValidateTable(
 ColumnOne VARCHAR(1),
 ColumnTwo VARCHAR(2)
)


CREATE PROCEDURE stp_ReturnTable
AS
BEGIN

 SELECT ColumnOne
  , ColumnTwo
 FROM ValidateTable

END


EXEC sys.sp_refreshsqlmodule 'stp_ReturnTable'


ALTER TABLE ValidateTable DROP COLUMN ColumnTwo


EXEC sys.sp_refreshsqlmodule 'stp_ReturnTable'

Identify Issues with sp_refreshsqlmodule

Microsoft offers the useful procedure sys.sp_refreshsqlmodule for identifying these possible issues and using it to check object validity can minimize possible mistakes of changing objects without ensuring that all dependencies remain valid. In this example, we'll use stored procedures as an example of how to check validity; keep in mind that depending on your environment, you may want to expand this to check other objects. Using .NET PowerShell makes it easy to perform this check on multiple servers and databases with the assumption that we have a database on each server for logging.

Connecting to a SQL Server Instance using PowerShell

We can begin connecting by using the SMO library, but note that the below code is deprecated (though it will work in PowerShell 3.0):

$s = "SERVER\INSTANCE"
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$serv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $s

I write the above code because you may see it frequently all over the web. The below code is better to get in the habit of typing because it uses PowerShell's built in function Add-Type:

$s = "SERVER\INSTANCE"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$serv = New-Object Microsoft.SqlServer.Management.SMO.Server($s)

Note that the dll may be in a different SQL Server folder; for instance, on one of my 2012 SQL Servers, it is under C:\Program Files (x86)\110\SDK\Assemblies\. If in doubt, search for Microsoft.SqlServer.Smo.dll in Windows.

Looping Through All Databases and Logging Issues Using PowerShell

Once we load the SMO library, we can loop through all the databases on a server and get the name, which the below code will do, skipping the master, model, msdb and tempdb system databases.

foreach ($d in $serv.Databases | Where-Object {$_.IsSystemObject -eq $false})
{
    $d.Name
}

Because we can loop through all the non-system databases, we want to loop through each database and check the validity of the objects in each - for this example, we'll only check the stored procedures. If the objects are invalid, we will log these objects. In some environments, we may script, then remove the object, and this script can be adjusted for other situations if we can make those assumptions. Since we'll be logging the validity, we'll create an example table for this using the database Logging; note that you would normally log this information in the database you use for tracking information. If you don't have a database setup, you should create a separate database.

USE Logging
GO
CREATE TABLE ObjectValidityTable(
 DatabaseName VARCHAR(100),
 ObjectName VARCHAR(250),
 CheckDate DATE DEFAULT GETDATE()
)

We will try to check the user stored procedure validity by looping through the procedures and calling sys.sp_refreshsqlmodule; if it fails, we will log it by inserting the information into the logging table. Note that we only want to check the user stored procedures, not the internal system procedures. If you face a situation where you want to check the system procedures, you would remove the check $proc.IsSystemObject -eq $false from the below code. Because we want to test if a procedure is valid by using a TRY-CATCH approach, we will use two SQL Commands with the .NET library (and use a Sql Connection with the .NET library). The below code shows this:

$s = "SERVER\INSTANCE"
Add-Type -Path "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"
$serv = New-Object Microsoft.SqlServer.Management.SMO.Server($s)

foreach ($db in $serv.Databases | Where-Object {$_.IsSystemObject -eq $false})
{
    $d = $db.Name

    foreach ($proc in $db.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false})
    {
        $o = $proc.Name
    ## Gets the schema for the procedure
        $sc = $proc.Schema

        $scon = New-Object System.Data.SqlClient.SqlConnection
        $scon.ConnectionString = "SERVER=" + $s + ";DATABASE=" + $d + ";Integrated Security=true"
        $trycmd = New-Object System.Data.SqlClient.SqlCommand
        $catchcmd = New-Object System.Data.SqlClient.SqlCommand
   ## We refresh the object with the schema.table
        $trycmd.CommandText = "EXECUTE sys.sp_refreshsqlmodule '$sc.$o'"
        $trycmd.Connection = $scon
        $catchcmd.CommandText = "INSERT INTO Logging.dbo.ObjectValidityTable (DatabaseName,ObjectName) VALUES ('$d','$o')"
        $catchcmd.Connection = $scon
 
        try
        {
            $scon.Open()
            $trycmd.ExecuteNonQuery()
 
        }
        catch
        {
            $catchcmd.ExecuteNonQuery()
        }
        finally
        {
            $scon.Close()
            $scon.Dispose()
        }
    }

}

After we call this, we will see PowerShell return -1 for valid objects and 1 for invalid objects.

Looping Through All Databases on Multiple Servers

Finally, we can wrap this in a function so that we can loop through multiple servers (assuming these servers all have the same logging database) and making sure that we pass in the location for the Microsoft.SqlServer.Smo dll:

Function CheckObjectValidity ($server, $smolibrary)
{
    Add-Type -Path $smolibrary
    $serv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
    foreach ($db in $serv.Databases | Where-Object {$_.IsSystemObject -eq $false})
    {
        $d = $db.Name
        foreach ($proc in $db.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false})
        {
            $o = $proc.Name
   ## Gets the schema for the procedure
            $sc = $proc.Schema
 
            $scon = New-Object System.Data.SqlClient.SqlConnection
            $scon.ConnectionString = "SERVER=" + $server + ";DATABASE=" + $d + ";Integrated Security=true"
            $trycmd = New-Object System.Data.SqlClient.SqlCommand
            $catchcmd = New-Object System.Data.SqlClient.SqlCommand
   ## We refresh the object with the schema.table
            $trycmd.CommandText = "EXECUTE sys.sp_refreshsqlmodule '$sc.$o'"
            $trycmd.Connection = $scon
            $catchcmd.CommandText = "INSERT INTO Logging.dbo.ObjectValidityTable (DatabaseName,ObjectName) VALUES ('$d','$o')"
            $catchcmd.Connection = $scon
 
            try
            {
                $scon.Open()
                $trycmd.ExecuteNonQuery()
            }
            catch
            {
                $catchcmd.ExecuteNonQuery()
            }
            finally
            {
                $scon.Close()
                $scon.Dispose()
            }
        }
    }
}
CheckObjectValidity -server "OURSERVER\OURDATABASE" -smolibrary "C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

In those environments with numerous database objects that haven't been validated, the above function assists in identifying possible objects that need to be changed, or objects that should be scripted and removed. Best of all, with PowerShell, the task can be automated and logged.  After the PowerShell script is run, just query the Logging table that was created to see which objects have reference issues.

Next Steps

• Check your current test environment - you may be surprised what you find.
• Commit to using source control for your databases. While the above function provides a useful tool, nothing beats source control.
• This system stored procedure does not work for SQL Server 2014 natively compiled stored procedures.
• Per Microsoft's documentation, this system stored procedure only identifies issues when the definition of the referenced object is different then what it expects.  It does not identify issues when the referenced object has been dropped.
• Take a look at using schema binding if you want to ensure that when underlying objects are changed they don't break the referencing object.

About the author

Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips