How Do I Find A String In Any Stored Procedure

This is an article about references and looking for clues. I originally wanted to title this article “Dude, Where’s My Code” but I had a feeling that:

A: Not many would catch the 1990’s movie reference.

B: I never actually saw “Dude, Where’s My Car” and I'm likely better for it.

C: I was having a hard time handling the fact that people would not get a reference to a 1990’s movie and that I’d just end up feeling really, really old.

But when it comes for trying to figure out where we used specific code or where we made reference to a specific object you will understand why I created this stored procedure the article focuses on as we proceed.

A Not-As-Brief-As-It-Should-Be Background on the Why

Long ago, one of my first articles focused on searching for specific columns to identify what table (or tables) a column may reside within a database using the sys.all_columns system catalog view. (LINK)

The reasoning: as a DBA working in healthcare I had to support over 2,000 separate databases developed both internally and by hundreds of independent software vendors (ISVs). Each development company – and Developer within a company – has her or his own style of coding. Finding issues within code that you’re not familiar with is one of those responsibilities you find yourself involved in as a data professional on a regular basis. The gist of that early article was identifying where columns existed for the purpose of debugging an issue affecting uptime of a healthcare product.

Now here I am years later in a new role at a new company. I find myself still using the code from that article but I also needed to come up to speed with how the development teams are utilizing stored procedures throughout the product since I now had responsibility over not just database administration but engineering and coding in my new joint role as both a DBA and DBE (Engineer).

Creating a search script to identify where specific objects, coding structures, or predicates were involved was critical for me to troubleshoot issues and develop new code.

A Stored Procedure to Search Stored Procedures

I look at coding like I do presenting: there should be a goal of what you want to accomplish for the time you’re spending when creating content of any kind. In this case the goal is to create a stored procedure that can be used to hunt for any reference to an object, code structure, etc. All that matters is that you’ve a string you want to search throughout all databases to return results for any stored procedure using the string we’re passing in as a variable for searching.

Just knowing the stored procedure name is not good enough. Therefore the end result will also include the following columns:

In order to build out this stored procedure we need to rely upon a few system objects: two system catalog views and one undocumented system stored procedure.

System Catalog Views:

sys.procedures – this system view provides metadata about all stored procedures in a database. It’s database-scoped which means each database has a sys.procedures catalog view and it only returns results for the contents of the database in which it is hosted.

sys.syscomments – this system view stores the code text for each stored procedure. As is the case with sys.procedures, sys.comments is also database-scoped so it will only provide insight into its own database’s objects.

Un-Documented System Stored Procedure:

sys.sp_MSforeachdb - Since the two system catalog views are database-scoped we need a way to return results for this query against all databases. Microsoft SQL Server comes with an undocumented stored procedure in the master database that provides this functionality to do just that. You can pass in a parameter for the t-sql command you want to run against each database and results are returned as separate record sets for each database the code is executed against.

Putting it All Together

Now that we know what needs to go into the solution it’s time to look at how we put it all together. Let me just lay out the code below then we can look at the particulars and see an example of this in action.

USE iDBA;GOCREATE PROCEDURE search_sprocs @searchforthis NVARCHAR(512) AsDECLARE @search_text NVARCHAR(MAX)IF OBJECT_ID('tempdb..#search_results') IS NOT NULLBEGINDROP TABLE #search_results;ENDCREATE TABLE #search_results(the__database sysname NOT NULL,the__schema sysname NOT NULL,procedure__name sysname NOT NULL,procedure__text NVARCHAR(4000) NOT NULL,colid int NOT NULL)SELECT @search_text = 'USE ?; INSERT INTO #search_results (the__database, the__schema, procedure__name, procedure__text, colid) SELECT db_name() AS the__database, OBJECT_SCHEMA_NAME(P.object_id) AS the__schema, P.name AS procedure__name , C.text AS procedure__text, C.colidFROM sys.procedures P WITH(NOLOCK)LEFT JOIN sys.syscomments C ON P.object_id = C.idWHERE C.text LIKE ' + '''' + '%' + @searchforthis + '%' + '''' + ';'EXEC sys.sp_MSforeachdb @command1 = @search_text;SELECT the__database , the__schema , procedure__name , procedure__text FROM #search_results ORDER BY the__database, the__schema, procedure__name, colid;GO

Breaking this down the first thing you’ll likely notice is that I’m creating this stored procedure in a database called iDBA. Anyone who reads my articles over the years knows that this is the database I use for all my administrative scripts. You may have a different name for this database in your environments but it’s important to have a database you can use for such needs in order to keep non-system objects from leaking into the master database where lazy coders and DBAs who love shortcuts drop their code.

I’ve required a single parameter for this stored procedure to pass in the string you wish to search for as @searchforthis. Inside the script I’ll bookend the parameter with % symbols so you don’t need to go overboard with the search string if you know you can get to your needed results with a smaller block of search criteria.

This stored procedure will search through all databases so I want to create a temp table to store the results that will then get returned to the end user. That’s what #search_results is for. While the first four columns make sense at face value, the final column (colid) may seem odd. The reason we need to have that column (sourced from sys.syscomments) is because stored procedures can be simple or quite complex. The code that comprises a stored procedure may need to be stored in multiple rows in sys.syscomments as a result. Inside of sys.syscomments you can identify the object the text of a stored procedure belongs to by looking at the id column. It maps to object_id throughout the system tables. The colid value is a 1-based column that refers to the order of the comment(s) that comprise the full text of a stored procedure. If you were to query sys.syscomments directly you may see multiple rows for id = 123456 which reference some object_id for a stored procedure. Each row for id = 123456 in sys.syscomments will have an escalating value for colid that orders the full text of the stored procedure so id = 123456, colid = 1 would be followed by id = 123456, colid = 2 and id = 123456, colid = 3… until the full command that comprises the stored procedure is covered.

So what this stored procedure ultimately does is builds a dynamic query joining sys.procedures and sys.syscomments on object_id to id (as explained above) where the comments contain the search criteria you’re hoping to find. This query text is then passed into the sp_MSforeachdb undocumented stored procedure as a parameter and executed one database at a time. All “hits” for the databases on your server get dropped into a temp table and returned when all databases have been queried.

Seeing it in Action

After creating the stored procedure I’m going to run the following query. Since I specify the database name it doesn’t matter what database is currently active when I run the script:

EXEC iDBA.dbo.search_sprocs 'backupset';

In this case I’m using ‘backupset’ because I know I’ve written stored procedures in multiple databases that reference that view in the msdb database for identifying backup metadata. I also expect to get hits in this case in system databases because I’m using a system view’s name as the search text.

The results look something like this:

Conclusion

While there are third party tools available to provide the same functionality as what we did here. Not everyone has the budget to purchase them. This is a simple code construct that is quite extensible and can save significant amounts of time when you’re searching for references throughout any number of databases on a SQL Server.