Using sql_modules System Catalog View to Search Function and Stored Procedure Definitions

In a recent article I presented you with an option for searching for any string of t-sql text inside your stored procedures; for that I used sys.syscomments. A point was raised by a reader that sys.syscomments, being a System Compatibility view (which has its origins dating back to pre-SQL Server 2005 days) has been "deprecated" for quite some time. While it's not true that System Compatibility views are deprecated they do in fact only exist in order to satisfy development by SQL Professionals against SQL Server 2000 and earlier versions when the master database contained table by the same name as these compatibility views. Hence the "compatibility" in the name. These views will never change in schema, be updated, or likely removed.

When SQL Server 2005 wa released an the advent of Compatibility views dawned so did the existence of SQL Server Sytem Views. These are views defined to better match the structure and improved user-friendly naming conventions of SQL Server 2005 - specifically the naming convention used in the Dynamic Management Objects exposed in that release and supported since. The schemas of the System Catalog Views will continue to evolve with the product and it was one of these Catalog Views that my reader brought to light as a better solution than sys.syscomments. I tend to agree.

Why sql_modules?

One of the flaws in using sys.syscomments was the data type used for the column hosting the sql text used in the object. It's the main column of interest when searching for text used in the definition of a SQL object. The defined data type was varbinary with a limit in size of 8000 bytes. Because of this limitation there is another column in sys.syscomments called col_id which sets an order should the definition of an object be greater than 8000 bytes. This means that you also need to return the col_id for the sake of piecing together the breadcrumbs of sql text for possible large definitions.

It also means - as our reader pointed out - that if the search term spans the demarcation line at the 8000 byte location you'll not have any results returned.

By contrast, sql_modules stores the definition in the definition column with a data type of nvarchar(max) removing the concern about limit at the 8000 byte point. You remove the need to stitch together results, or concerns about missing a valid hit on a result for searching due to the search string spanning two records at that 8000 byte point.

While We Are Making Changes: Use sp_foreachdb Also

The process for using these search mechanisms I'm discussing in these articles requires you to search across all databases. In order to do so I used an undocumented system stored procedure in the first article: master.sys.sp_msforeachdb. There are inherent issues with relying on this stored procedure that may include skipping a database or too all together - not a good thing when you want to search all databases, is it?

Quite a few years ago, fellow SQL Server MVP Aaron Bertrand took the time to create a better stored procedure for searching all databases called sys.sp_foreachdb. I've spoken of this tool in my articles here when needed and this is really a better solution for the process than relying on the undocumented Microsoft-provisioned stored procedure. The solution below essentially makes changes to the original solution provided here with use of sys.sql_modules and sys.sp_foreachdb. I used the MS-provide object so as to not force you to install or create any other dependent objects. However, now that you've seen the process for searching for t-sql definitions for objects it seems like the right time to have you install Aaron's solution for better cross-database query execution.

A Better Solution for Searching for Object Definitions

The following code accomplishes what was set forth in the first article but with a more-reliable cross-database searching mechanism and removing the concern about the 8000 byte limit exposed in sys.comments:

USE iDBA;GOCREATE PROCEDURE search_sproc_text @searchforthis NVARCHAR(512), @object_type VARCHAR(2) = NULL, @is_ms_shipped BIT = 0 ASDECLARE @search_text NVARCHAR(MAX)IF OBJECT_ID('tempdb..#search_results') IS NOT NULLBEGIN    DROP TABLE #search_results;ENDCREATE TABLE #search_results    (    the__database sysname NOT NULL,    the__schema sysname NOT NULL,    object__name sysname NOT NULL,object__type NVARCHAR(120) NOT NULL,is_ms_shipped BIT NOT NULL,    sql__text NVARCHAR(MAX) NOT NULL    )IF @object_type IS NULLBEGINSELECT @search_text ='USE ?; INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text) SELECT db_name() AS the__database, OBJECT_SCHEMA_NAME(O.object_id) AS the__schema, O.name AS object__name , O.type_desc AS object__type, O.is_ms_shipped, M.definition AS sql__textFROM sys.objects O WITH(NOLOCK)LEFT JOIN sys.sql_modules M ON O.object_id = M.object_idWHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND M.definition LIKE ''%' + @searchforthis + '%''' + ';'ENDELSEBEGINSELECT @search_text ='USE ?; INSERT INTO #search_results (the__database, the__schema, object__name, object__type, is_ms_shipped, sql__text) SELECT db_name() AS the__database, OBJECT_SCHEMA_NAME(O.object_id) AS the__schema, O.name AS object__name , O.type_desc AS object__type, O.is_ms_shipped, M.definition AS sql__textFROM sys.objects O WITH(NOLOCK)LEFT JOIN sys.sql_modules M ON O.object_id = M.object_idWHERE O.is_ms_shipped = ' + CAST(@is_ms_shipped AS VARCHAR(1)) + ' AND O.type = ''' + @object_type + '''' +'AND M.definition LIKE ''%' + @searchforthis + '%''' + ';'ENDEXEC dbo.sp_foreachdb @command = @search_text;SELECT the__database    , the__schema    , object__name, object__type, is_ms_shipped    , sql__text FROM #search_results ORDER BY the__database    , the__schema    , object__nameGO

As I mentioned in the related article you'll notice I'm creating this stored procedure in a maintenance database in order to keep the master database clean. In doing so when I make a call to this stored procedure I need to fully qualify it with the database name. If you want to instead alter the query above to host it in master and be able to call it from any database context you can do so by changing the database name to master and prefixing the stored procedure name with "sp_".

Since we were making modifications for both use of sys.sql_modules as well as sp_foreachdb I also took the time to add two optional parameters should you wish to filter your results by a specific object type (see table below for possible values for this parameter) and whether or not the objects to be searched should include system objects (is_ms_shipped = 1). You can also leave the parameter value unassigned (aka NULL) and you'll get results for any object type having a definition containing the search string.

Possible Values for Object Type

The following are the possible object type values for the @object_type parameter:

Examples

Example 1: No filtering for object type

In this first example I'll use the same criteria I did in the related article: searching for any reference to "backupset". I'll not filter for any object type and I only want to see user-defined objects in my results. Any of the three following examples provide the identical results:

EXEC iDBA.dbo.search_sproc_text 'backupset';EXEC iDBA.dbo.search_sproc_text 'backupset', NULL, NULL;EXEC iDBA.dbo.search_sproc_text 'backupset', NULL, 0;

Example 2: Filter for only user-created stored procedures

In this next example I'll use the same criteria I did above but this time I only want to see system triggers:

EXEC iDBA.dbo.search_sproc_text 'backupset', 'TR', 1;

Conclusion

Thanks to an insightful reader I've been able to improve upon the earlier code and also add functionality to filter on objects supplied by Microsoft or user-created objects as well as by object type. This shows how you can take any code you find here or elsewhere online and give it your own twist. While you're doing so I always invite you to share it here with us or blog about it on your own sites.