In 2009, Adam Machanic (b|t) started the monthly blog event known as T-SQL Tuesday. One person picks a topic, and everyone is invited to blog about it on the second Tuesday of the month. The July 2018 host is Bert Wagner (b|t), and his chosen topic is: Code You Would Hate To Live Without. Talk about a broad topic! I could fill a book on that topic, and the same probably goes for many others.

But since my time is limited, I have decided to cherry-pick just two of my favorite scripts. This post is about the first: sp_metasearch.

Impact analysis

This might come as a surprise to some. There are actually companies that do not have perfect documentation of everything they have in their database! And for some reason, I always find these companies as my customers. And that means that, before I can make an innocent change such as changing a data type or dropping a few tables, I need to have a way to check which objects might be affected.

The code to search the DMOs is not very hard. But typing it over and over again quickly becomes tedious. That’s why I decided to turn this in a stored procedure.

sp_metasearch

Here is the code to create the stored procedure sp_metasearch. Most of the code is fairly simple if you know the structure of the various dynamic management objects used. I will briefly touch on some points below the code.

-- Create the stored procedure in the master database
USE master;
GO
-- Yes, I use the sp_ prefix. Yes, this is deliberate. See explanation in blog.
CREATE OR ALTER PROCEDURE sp_metasearch @SearchString nvarchar(200)
AS
SET NOCOUNT ON;

DECLARE @SearchWildcard nvarchar(202) = N'%' + @SearchString + N'%';

SELECT      o.type_desc              AS Type,
            SCHEMA_NAME(o.schema_id) AS SchemaName,
            o.name                   AS ObjectName,
            CASE WHEN o.type_desc = N'SQL_TRIGGER'
                     THEN OBJECT_NAME(o.parent_object_id)
                 ELSE N''
            END                      AS ParentObjectName
FROM        sys.all_sql_modules AS asm
INNER JOIN  sys.objects         AS o
   ON       o.object_id = asm.object_id
WHERE       asm.definition LIKE @SearchWildcard
ORDER BY    Type,
            SchemaName,
            ParentObjectName,
            ObjectName;

SELECT      j.name       AS JobName,
            c.name       AS Category,
            js.step_id   AS Step,
            js.step_name AS StepName
FROM        msdb.dbo.sysjobsteps   AS js
INNER JOIN  msdb.dbo.sysjobs       AS j
   ON       j.job_id      = js.job_id
LEFT JOIN   msdb.dbo.syscategories AS c
  ON        c.category_id = j.category_id
WHERE       js.command LIKE @SearchWildcard
AND         js.database_name = DB_NAME()
ORDER BY    JobName,
            Step;
GO
-- Mark as system object -- SEE BELOW!!!
EXEC sys.sp_MS_marksystemobject @objname = N'sp_metasearch';
GO

You may notice that (1) I create the stored procedure in the master database; (2) its name starts with the “forbidden” sp_ prefix, and (3) I use the undocumented stored procedure sp_MS_marksystemobject to make SQL Server believe that this is a system object. I do that deliberately, because the exact combination of these three ingredients allows me to call the stored procedure from any database and have it execute within the context of that database, which for this specific functionality is a great feature! But yes, I am aware of all the risks I take doing this, and so should you if you decide to copy this code snippet!

Example usage and results

Once the stored procedure is created, using it is easy. Let’s say I am working on the AdventureWorks2017 database and I need to change the data type of the SubTotal column in table Purchasing.PurchaseOrderHeader. Before I do that I want to review all code that might potentially be affected so I open a query window and execute this code:

EXEC sp_metasearch 'SubTotal';

The results on my system are as follows:

The two result sets show me all objects I need to verify. For triggers, the ParentObjectName tells me what table the trigger is defined on. The results are ordered in a way that make it easy for me to work down the list of items to check in SSMS.

Limitations and variations

I wrote this code for personal use, and as such it has a number of limitations that you need to be aware of if you want to “steal” it. I also often write variations on this code for other customers, to make it more suited to their environment.

Textual search

As you have seen in the code, the search uses a simple brute-force text comparison. A benefit of this is that I can search for everything: table and column names, but also subsets of those names, longer phrases, or whatever else I fancy (e.g. “—TODO” to find all my unfinished business!!).

But this strength is also a weakness because it increases the risk of false positives, for instance in code that was removed by commenting, or when a search phrase is part of a common word. One day a customer wanted to change the codes they use in a code table for product types and they were unsure whether there might be hard-coded references to these codes. One of the codes was “PDA”. Using sp_metasearch to find potential locations returned a huge list: each and every stored procedure that includes the keyword UPDATE. Fun times!

Current database

You may also have noticed that this code limits the search to the current database only, and that it only searches in SQL Agent jobs that start in the current database. That made sense at the customer where I first developed and used this stored procedure, where each database was completely self-contained and there were no interactions between databases.

I have also worked at customers where cross-database queries were common; here it made more sense to remove the database filter in the second query, and to use sp_foreachdb to search for objects in all databases on the instance, so that all databases on the current instance are searched. At one of my current customers I even use a central management server to connect a single window to all relevant servers and execute (a variation on) this code on multiple instances at once.

Missing areas

There are also some limitations in where I do and do not search, because I built this just for my needs. I do search the DMO “all_sql_modules”, which contains the text of stored procedures, views, user-defined functions, and triggers. I also search in the commands of all SQL Agent jobs. But that’s it. I will never find matches to the search string in your personal library of helpful T-SQL and Powershell scripts. Or in the SSIS catalog. Or in the SSRS report collection. Or in synonyms. And depending on how you exactly a dynamic SQL string is built, I miss that too.

And those are just the omissions that I am aware of, there may be other locations where you could find code that I have never run into yet.

It’s not that these issues are unfixable. Most can be fixed (though searching your personal library of SQL scripts might pose a bit of an issue). I just have not yet worked for customers where I needed to do this. I know this when I use sp_metasearch so it won’t catch me by surprise. And now you know this as well!

Conclusion

If you like what sp_metasearch can do for you, then feel free to copy and use it. If you see errors in the code, or if you extend the code to search in other locations as well, then I would appreciate hearing from you. Please use the comment form below to share how you used or improved this code!