Finding SQL Server Objects that don't conform to Standards using PowerShell

By:   |   Updated: 2014-10-07   |   Comments (1)   |   Related: More > Database Administration


Problem

An old idiom states that an ounce of prevention is worth a pound of cure and I haven't met a DBA who disagrees. We tend to focus our efforts on some prevention in many cases. One particular case I've seen become more common with some of my clients, as well as other environments where DBAs have uttered those dreaded words, "Our environment is a mess." I remember speaking with an awesome Oracle DBA who was managing both Oracle and SQL Server environments who told me, "Our environment is a mess - and it's hard to know who's behind the mess; we don't know who created this or that object and why, and we don't even know if half the code in these environments is even necessary any more." How can we begin to think, plan and - most importantly - act on building our ounce of prevention?

Solution

Most clients agree that ideally they want to know who created an object and what the object does. For instance, if an environment has a stored procedure called MyProcedure17, that indicates nothing about what it does. It becomes worse when we look at the procedure code and we don't find a name or description attached to it. Is that object used? If we needed to find out, who would we call? If it failed, is it important?

Obviously, this could become fairly deep (as far as a solution) because, depending on our environment, we may need to know even more than that basic information. For the sake of focus and providing ideas, I will focus this tip on stored procedures because of what they can do - SELECT, UPDATE, DELETE, INSERT, and run a myriad of commands, provided that the user running them has permission to do so. This same logic can be expanded though - to views, functions, triggers, etc., if needed; and for some environments (OLAP), I would highly suggest expanding it to views. Also, we'll look at two specific cases: a procedure carries the appropriate environment convention with a proper name and author, or - if the author leaves - it has a description (and now with modern technology, a link to a page describing what it does is even better).

Naming Conventions for SQL Server Objects

For some environments, the name of an object means a lot - it states what it is, what database it belongs to, and summarizes what it does. For instance, stp_BulkInsert alerts in the beginning that it is a stored procedure and that it INSERTS. The lack of it having a database name attached to it means that it could be universally used by any database - if an object only operates within one database, a good naming convention might let users know that. For instance, stp_HFT_InsertValues, tells us that our object is a stored procedure belonging to the database HFT and inserts values.

Getting Alerts for Stored Procedure that don't conform

If we want our environment to alert us about procedures like MyProcedure17, we can automate this process in PowerShell and resolve the problem quickly so that our environment doesn't slowly become a mess. Simply put: if we prevent developers from using any name they want, we can maintain an organized environment, and this is easily possible even for start ups, as we'll see. Finally, by implementing a process like this, we can more easily identify unused processes, such as stored procedures, like this tip covers.

First, we will want to check the name. For this example, we want our procedures to indicate that they are procedures, as this will make things easy in source control, or when we look at other articles like identifying where procedures aren't used. If a procedure can have the same name as a table, automating what is used and what isn't used becomes an added challenge. Since our example uses the naming convention of stp_ to indicate a procedure (though it could be anything like proc_ or ending in _proc, we will find all procedures that don't have that leading their name and which are user stored procedures:

foreach ($p in $db.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false})
{
    $pName = $p.Name
 if ($pName.Substring(0,4) -ne "stp_")
 {
  ## Do something
 }
}

What we're doing here is first detecting if the user stored procedure matches our naming convention - if it doesn't, it falls inside the if clause. We will then expand that to include detecting whether it links to a documentation page. Note that this is my preference because on a documentation page, we can specify a lot more than using comments. Because a documentation page will lead with "https://" it makes it easier to find and identify if one exists (if we use a company domain, we can expand it). Finally, I prefer a documentation page because people move on and if they leave, a documentation page helps future developers - we can't always contact the developer behind a piece of code and ask, so it's best to have an explanation for why something was constructed in a certain way. As a living example of how this can become a problem, two DBAs and I recently met about code management in their environment, and even with source control implemented, when processes broke, it took them (1) hours to isolate the problem, and (2) any change they implemented often had effects on other processes. Even with comments left in the code, the overall architecture hadn't been documented on even a minor level causing newer developers to spend more time trying to figure out existing and, possibly broken, processes instead of being able to isolate the problem quickly.

In our example, the details of the procedure would go in the header, not in the body of text - if you wanted the details in the body, you would change $p.TextHeader.Replace("'","") with $p.TextBody.Replace("'","").

foreach ($p in $db.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false})
{
    $pName = $p.Name
 $pTxt = $p.TextHeader.Replace("'","")
 if (($pName.Substring(0,4) -ne "stp_") -or ($pTxt -notlike "*Details: https://*"))
 {
  ## Do something
 }
}

In the cases we don't point to a documentation page and want a description:

foreach ($p in $db.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false})
{
    $pName = $p.Name
 $pTxt = $p.TextHeader.Replace("'","")
 if (($pName.Substring(0,4) -ne "stp_") -or ($pTxt -notlike "*Description:*"))
 {
  ## Do something
 }
}

In both cases, we have set a standard for our environment involving stored procedures. Part of our preventative measures is identifying any procedure that doesn't fall into this realm. In addition, if we run this every night (a suggestion), we can easily spot an exception quickly and have it resolved. "George, I saw that you wrote a new procedure and it doesn't match our convention. We have a How-To page telling you how to make sure your procedure fits our environment, here [How-To page]. Thanks!" or if we have multiple developers per database/server, we can email the entire team, "Team, we've identified a stored procedure without documentation called MyProcedure17. If you're the developer behind this, please correct per our documentation at [documentation link]. If this isn't corrected within a week, we will script and remove this procedure. Thanks!"

Getting Alerts for Other Objects that don't conform

We can also expand this logic if we want to include views, functions, triggers, etc.; for instance, the code below this looks at views as an example, which some environments will find benefit to adding:

foreach ($v in $db.Views | Where-Object {$_.IsSystemObject -eq $false})
{
    $vName = $v.Name
 $vTxt = $v.TextHeader.Replace("'","")
 if (($vName.Substring(0,5) -ne "view_") -or ($vTxt -notlike "*Details: https://*")
 {
  ## Do something
 }
}

As we'll see later, we can expand the final function to include objects we tend to use frequently

Getting More Details in the Alert

Now that we've identified the procedures (or other objects) that we know don't match our environment standards, we want to get the time they were created and compare that with the current time. In this example, I use a timeframe of seven days, though your environment may use a longer or shorter timeframe. Never assume that you can use someone else's exact timeframe unless you're the Senior DBA and know exactly when you can apply these solutions.

## These variables will be added to the first part of our function
$now = Get-Date
$evme_dev_email_list = "John Doe , Jane Doe "
$evme_subject = "WARNING: Object Will Be Deleted"

## We'll be looking at the date the object was created and how that compares to now
$proc_max = ($p.CreateDate).AddDays(7)
 if ($proc_max -le $now)
 {
  $drop_array += $p.Name
 }
 else
 {
  $evme_body = "Object, $pName, has been named incorrectly and will be removed seven days from the date that it was created.  If you created this object, correct immediately, following our convention at https://www.OurEnvironment.com/OurNamingConvention.html."
  ## The below is PowerShell's built in Send-MailMessage function
  Send-MailMessage -From "[email protected]" -To $evme_dev_email_list -SmtpServer "smtp.OurEnvironment.com" -Subject $evme_subject -Body $evme_body -Priority High
 }

## Outside of our loop, we will loop over our array, script and remove the procedures
foreach ($drop in $drop_array)
{
 $db.StoredProcedures["$drop"].Script() | Out-File "C:\Scripts\$drop.sql"
 $db.StoredProcedures["$drop"].Drop()
}

In .NET, we cannot modify a collection while looping over it, so the approach I like to use is to add the items we find to an array and then go back and modify the objects in the array. If it's been longer than seven days since the procedure was created, it's scripted and removed, otherwise, the developer list receives an email letting them know that it will be removed from the date it was created. This part of the process will vary, depending on your environment, and you can change as necessary. So let's look at the function pointed at procedures all together:

Function MaintainEnvironment_Procedures ($server, $smolibrary)
{
    Add-Type -Path $smolibrary
    $now = Get-Date
    $evme_dev_email_list = "John Doe , Jane Doe "
    $evme_subject = "WARNING: Object Will Be Deleted"
    $serv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
    foreach ($db in $serv.Databases | Where-Object {$_.IsSystemObject -eq $false})
    {
        $d = $db.Name
        $drop_array = @()
        foreach ($p in $db.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false})
        {
            $proc_max = ($p.CreateDate).AddDays(7)
            $pName = $p.Name
            $pTxt = $p.TextHeader.Replace("'","")
 
            if (($pName.Substring(0,4) -ne "stp_") -or ($pTxt -notlike "*Details: https://*"))
            {
                if ($proc_max -le $now)
                {
                    $drop_array += $p.Name
                }
                else
                {
                    $evme_body = "Object, $pName, has been named incorrectly and will be removed seven days from the date that it was created.  If you created this object, correct immediately, following our convention at https://www.OurEnvironment.com/OurNamingConvention.html."
                    Send-MailMessage -From "[email protected]" -To $evme_dev_email_list -SmtpServer "smtp.ourenvironment.com" -Subject $evme_subject -Body $evme_body -Priority High
                }
            }
        }
 
        foreach ($drop in $drop_array)
        {
            $db.StoredProcedures["$drop"].Script() | Out-File "C:\Scripts\$drop.sql"
            $db.StoredProcedures["$drop"].Drop()
        }
    }
}

MaintainEnvironment -server "OurEnvironmentServer\OurEnvironmentInstance" -smolibrary "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

What if we want to look at multiple objects - for instance, functions, procedures and views? Because the naming convention will be different for each (for instance, a procedure might start with stp_ whereas a view will start with view_), we will look at the headers only to ensure they have documentation, and note the line below ## Add or subtract the objects we use regularly here as items in the below array: as we can expand functionality:

Function MaintainEnvironment_HeadersOnly ($server, $smolibrary)
{
    Add-Type -Path $smolibrary
    $now = Get-Date
    $evme_dev_email_list = "John Doe , Jane Doe "
    $evme_subject = "WARNING: Object Will Be Deleted"
    $serv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
    
    ## Add or subtract the objects we use regularly here as items in the below array:
    $loop_objects = @("UserDefinedFunctions","StoredProcedures","Views")
    

    foreach ($db in $serv.Databases | Where-Object {$_.IsSystemObject -eq $false})
    {
        $d = $db.Name
 
        foreach ($loop in $loop_objects)
        {
            $drop_array = @()
            
            foreach ($lo in $db.$loop | Where-Object {$_.IsSystemObject -eq $false})
            {
                $lo_max = ($lo.CreateDate).AddDays(7)
                $lTxt = $lo.TextHeader.Replace("'","")
                $lName = $lo.Name
 
                if (($lo_max -le $now) -and ($lTxt -notlike "*Details: https://*"))
                {
                    $drop_array += $lName
                }
                else
                {
                    $evme_body = "Object, $lName, has been named incorrectly and will be removed seven days from the date that it was created.  If you created this object, correct immediately."
                    Send-MailMessage -From "[email protected]" -To $evme_dev_email_list -SmtpServer "smtp.OurEnvironment.com" -Subject $evme_subject -Body $evme_body -Priority High
                }
            }
 
            foreach ($drop in $drop_array)
            {
                $db.$loop["$drop"].Script() | Out-File "C:\Scripts\$drop.sql"
                $db.$loop["$drop"].Drop()
            }
        }
    }
}
MaintainEnvironment_HeadersOnly -server "OurEnvironmentServer\OurEnvironmentInstance" -smolibrary "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

Remember that objects, from procedures to tables to functions, aren't always structured the same way, so we should use caution when looping through multiple objects; from a maintenance standpoint, it can make as much sense to handle each object as its own; for instance, with views we may care little about documentation and with procedures we may want to check if the name has INSERT, UPDATE, etc. in it. Likewise, we don't have to drop the objects - an email alert may suffice in our environment, so we may be able to skip the scripting and dropping. The key is that environment maintenance is simple with PowerShell and we can use a library, like SMO, to identify issues as they are created and handle them accordingly, and we can see an example of this application in the below code, where we handle our procedures, views and tables separately, remove the time comparison, and simply look for the procedures, tables and views that don't match our environment definition, emailing an alert when we find them:

Function MaintainEnvironment_EmailAlert_ProceduresViewsTables  ($server, $smolibrary)
{
    Add-Type -Path $smolibrary
    $evme_dev_email_list = "John Doe , Jane Doe "
    $evme_subject = "WARNING: Object Will Be Deleted"
    $serv = New-Object Microsoft.SqlServer.Management.SMO.Server($server)
    foreach ($db in $serv.Databases | Where-Object {$_.IsSystemObject -eq $false})
    {
        $d = $db.Name
        foreach ($p in $db.StoredProcedures | Where-Object {$_.IsSystemObject -eq $false})
        {
            $pName = $p.Name
            $pTxt = $p.TextHeader.Replace("'","")
 
            if (($pName.Substring(0,4) -ne "stp_") -or ($pTxt -notlike "*ETL Details: https://*"))
            {
                $evme_body = "Where is the ETL documentation for $pName?  Please correct immediately."
                Send-MailMessage -From "[email protected]" -To $evme_dev_email_list -SmtpServer "smtp.OurEnvironment.com" -Subject $evme_subject -Body $evme_body -Priority High
            }
        }

        foreach ($v in $db.Views | Where-Object {$_.IsSystemObject -eq $false})
        {
            $vName = $v.Name
            $vTxt = $v.TextHeader.Replace("'","")

            if (($vName.Substring(0,4) -ne "view_") -or ($vTxt -notlike "*Fact table for Cube *"))
            {
                $evme_body = "What cube does $vName relate to?  Correct this immediately."
                Send-MailMessage -From "[email protected]" -To $evme_dev_email_list -SmtpServer "smtp.OurEnvironment.com" -Subject $evme_subject -Body $evme_body -Priority High
            }
        }
  
  foreach ($t in $db.Tables | Where-Object {$_.IsSystemObject -eq $false})
        {
            $tName = $t.Name

            if (if (($tName -notlike "*dtb*") -and ($tName -notlike "*ttb*"))
            {
                $evme_body = "Tables in our environment must have either dtb or ttb in them.   $tName does not, please correct immediately."
                Send-MailMessage -From "[email protected]" -To $evme_dev_email_list -SmtpServer "smtp.OurEnvironment.com" -Subject $evme_subject -Body $evme_body -Priority High
            }
        }
    }
}

MaintainEnvironment_EmailAlert_ProceduresViewsTables -server "OurEnvironmentServer\OurEnvironmentInstance" -smolibrary "C:\Program Files (x86)\Microsoft SQL Server\100\SDK\Assemblies\Microsoft.SqlServer.Smo.dll"

This shows the flexibility of how we can maintain our environment - we have our rules, now we just map them in PowerShell and point it to the applicable servers.

Conclusion

This approach saves time as developers move on - we have an environment where our objects follow our rules, and as DBAs we know that we can make certain assumptions because of these objects following our rules. Take, for instance, my article on source control - if we demarcate objects like stored procedures and views, we save a ton of energy when automating querying code. We lose tons of time and energy if we don't have a system like this, for instance a table called Currencies, a procedure called Currencies (or a derivative of currencies), and a view called Currencies (or, again a derivative of such). How could we automate such when we'd have a name conflict?

Next Steps
  • First, what objects does your environment use?
  • Second, how do you want the objects in your environment to be maintained as far as object names, documentation/details on objects, etc.?
  • Third, use some of the above code to test in an environment and change in accordance with what object rules you want.


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Tim Smith 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


Article Last Updated: 2014-10-07

Comments For This Article




Wednesday, December 31, 2014 - 10:31:33 AM - Rollin Shultz Back To Top (35804)

The more I learn about this whole deal of communicating data, the more I am convinced we need to make every layer so to speak an  independent program. Such independent programs should talk introduce each other. So then after a connection is established the database can tell the connectee, this is my information and I present it this way. It then can send a list of table names, column names (I despise the field nomenclature for columns), datatypes for each of those and stroed procedures with their name and description. Extra security can be had by disallowing certain functions like drop this or delete * from outside entities. The most harmful thing an outside program could do is update a record with wrong info.

The next layer could then expect this same communication from all databases, which would mean the program would work with all databases the same way. It would get the expected string of information, break it down into its component parts and slot all/only information it needs into its objects, whereby it can temporarily change datatypes and anything else it needs to serve its information to the independent UI.

Said layer could deliver all information to the UI in the form of another single string of information to be interpreted by the UI by way of some delimiter etc to which the UI would have n - textboxes, listboxes etc in it's own order and it could either expect the string to be delivered with that order or have its own interpreter to shuffle the string data around as it pleases. The UI intepreter would not need to work with the data as objects, just strings with tags that say where it goes. For example the first part of the info string is customer ID. In the database it is an integer, but when passed to the UI all information is a string. It will be passed back to the middle layer as a string which can format to the database as the database has already specified during its initial communication.

This really is the only way to go if we want true security and flexibility so that a database can be any kind and the other layers can be written in any language at any time. Though this sounds complicated, Is it really, in a time in which we are striving to create AIs that can think like humans, can layer independence be too difficult to achieve?














get free sql tips
agree to terms