Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Finding SQL Server objects that reference invalid objects


By:   |   Last Updated: 2014-06-23   |   Comments (2)   |   Related Tips: More > 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'

We currently haven't committed to source control and we have objects in production that may be mismatched with the objects they refer to.

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.


Last Updated: 2014-06-23


next webcast button


next tip button



About the author
MSSQLTips author Tim Smith Tim Smith works as a DBA and developer and also teaches Automating ETL on Udemy.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Saturday, September 27, 2014 - 10:48:51 AM - DonW Back To Top

Managing database changes, unlike managing application code changes, is often seen as less in need of source control because (theoretically) the database is backed up and changes can always be identified by comparing the production version with a restore in a test environment.  Huh?  How much effort and time does that take compared to looking at the history in source control?  Not only that, you don't see any intermediate changes that may need to be retained.  It's also a poor way to audit a database schema.

Granted, we database folks aren't used to using source control but once you get the everyone on board, it's a great tool.

We use source control where I work and I've learned to love it.  Until the keeper of the source control decides to delete a branch that they deem unworthy of retention.  Or maybe they do it out of ignorance or by mistake.  No matter the reason, the net effect is the same.  You've lost your history.  It has happened to the database group twice that I know of.

When it happened to us earlier this year I contacted the group that manages it.  "What?  We deleted your branch.  Oh, so sorry.  (Time passes.)  We've recreated it for you.  The history?  Get it back?  No, that's not going to happen."  Somehow I doubt such a cavalier attitude would apply if they deleted the branch that contained application code. 

Unlike database developers, application developers live and die by their source control repository, checking out the code modules they need to work on and checking them back in when finished and ultimately deployed from the repository during a code release.  We database people tend to go directly to the database as the ultimate source of truth.  The problem with that is that if database developers A and B are both working on the same stored proc, they don't necessarily know it and can overwrite one another's changes.  At best, when time comes to deploy code changes, two versions of the same procedure are presented and then someone has to figure out what they do and how to merge them.

Source control allows you to know who has checked out a module whether it has been checked back in, and what changes were made versus the previous version so you don't step on one another.

But using source control is pointless if the people who manage the repository don't take it seriously.  Is that a reason not to use it?  NO!!!  (Was I emphatic enough?)  The solution to maintaining the control in source control, one we are trying to get implemented before we bother starting to use it again, is to get permissions to manage your branch of the repository yourself or at the very least be on the list of people to be notified before they make any changes whatsoever to the repository.

Managed properly, source control will be your friend.  It allows you to see all the changes made to database objects without having to touch the database.  It also gives you a base for determining what changes are known (i.e., checked into source control) so that if a changes has been made to the database schema without proper authorization you can perform an audit to detect it and, if necessary, reverse it.  It also saves you from leaving large blocks of old, commented code for documentation purposes or, even worse, keeping _Old versions of objects in the database, cluttering it up and taking up space.


Tuesday, July 01, 2014 - 11:27:30 AM - Chris Back To Top

Thank you for your post. I made a slight adjustment to include other objects beyond stored procedures as well as introduced a stored procedure to handle the inserts and a common PS function for ease of use.

 

$s = "localhost"

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)

 

Function CheckObject

{

    Param ( $DatabaseName, $ObjectSchema, $ObjectName)

    $scon = New-Object System.Data.SqlClient.SqlConnection

    $scon.ConnectionString = "SERVER=" + $s + ";DATABASE=" + $DatabaseName + ";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 '$ObjectSchema.$ObjectName'"

    $trycmd.Connection = $scon

    $catchcmd.CommandText = "EXECUTE Audit.dbo.usp_ObjectValidationCheckInsert @DatabaseName = '$DatabaseName', @ObjectName = '$ObjectName', @CheckStatus = 0"

    $catchcmd.Connection = $scon

 

    try

    {

        #Write-Host $o

        $scon.Open()

        $trycmd.ExecuteNonQuery() | Out-Null

 

    }

    catch [Exception]

    {

        $ExceptionMessage = $_.Exception.Message -replace "'", "''"

        Write-Host $DatabaseName"."$ObjectSchema"."$ObjectName ":"

        Write-Host $_.Exception.Message

        $catchcmd.CommandText += ", @Exception = '$ExceptionMessage'"

        $catchcmd.ExecuteNonQuery()  | Out-Null

    }

    finally

    {

        $scon.Close()

        $scon.Dispose()

    }

}

 

 

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

        $sc = $proc.Schema

 

        CheckObject $db.Name $sc $o

    }

 

    foreach ($trig in $db.Triggers | Where-Object {$_.IsSystemObject -eq $false})

    {

        $o = $trig.Name

        $sc = $trig.Schema

 

        CheckObject $db.Name $sc $o

    }

 

    foreach ($udf in $db.UserDefinedFunctions | Where-Object {$_.IsSystemObject -eq $false})

    {

        $o = $udf.Name

        $sc = $udf.Schema

 

        CheckObject $db.Name $sc $o

    }

 

    foreach ($view in $db.Views | Where-Object {$_.IsSystemObject -eq $false})

    {

        $o = $view.Name

        $sc = $view.Schema

 

        CheckObject $db.Name $sc $o

    }

 

}


Learn more about SQL Server tools