Finding Stored Procedures or Functions Contains Certain Table or SQL Text

Sometimes, there are situations where we need to find out a particular or list of stored procedures or functions that contain certain objects or SQL text. There are several ways to accomplish this purpose.

SQL Server information schema view (INFORMATION_SCHEMA) provides internal metadata of SQL Server. One of the useful view for this purpose is INFORMATION_SCHEMA.ROUTINES. This view returns each row for each stored procedure and function of current database. The column, ROUTINE_DEFINITION, in the view returns the first 4000 characters of the stored procedure or function definition (syntax).

USE msdb;
GO

SELECT ROUTINE_NAME, ROUTINE_DEFINITION, LEN(ROUTINE_DEFINITION) as DEFINITION_LENGTH
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%dbo.sysjobs%'
ORDER BY DEFINITION_LENGTH DESC
GO

If you notice, the maximum length returned is 4000. Please be aware that this view only contains definition up to first 4000 characters. Due to this limitation, this view may return incorrect result set of your search criteria, especially when there are stored procedures and functions contains more than 4000 characters.

There is a more reliable way to achieve this. Since SQL Server 2005, SQL Server includes a catalog view, sys.sql_modules, that returns a row for each procedure. function, view, trigger and other types. This view has a column, definition, that contains the SQL text of the module.

USE msdb;
GO

SELECT OBJECT_NAME([object_id]) AS [Module], LEN([definition]) AS definition_length, [definition]
FROM sys.sql_modules
WHERE [definition] LIKE '%dbo.sysjobs%'
ORDER BY definition_length DESC
GO

Notice that the definition length returned from this view is not limited with 4000 character like the previous view.

However, there is one small problem. Not the search result returned from the view, but to see the entire syntax of the stored procedure or function. Usually if you right click on the definition of the desired row and select copy (or simply Ctrl+C) to copy the definition, you should be able to paste it on the new query windows and review the entire SQL text. However, if the SQL text is too long (I have not discovered the the limitation), either the copy action doesn't copy the entire SQL text or paste action doesn't paste the entire SQL text, only partial SQL text is paste in the new query window.

In this example, trying to copy and paste the definition of is sp_sysutility_ucp_configure_policies in the new query window does not paste the entire SQL text of sp_sysutility_ucp_configure_policies stored procedure.

There are a few ways to view the entire SQL text of the procedure. You can browse those the object from SSMS and select modify, or use one of the system stored procedure, sp_helptext, to return the entire SQL text of the stored procedure.

Before executing the command, make sure you choose the 'result to text' option.

sp_helptext 'sp_sysutility_ucp_configure_policies'

Now you can copy the result and paste it in the new window and you still have the entire SQL text.

In addition to finding existing stored procedures or functions meet the search criteria, you could also examine previously executed and cached stored procedure, functions or ad-hoc queries if they meet the search criteria. Begin in SQL Server 2005, there is a dynamic management view, sys.dm_exec_cached_plans and a dynamics management function sys.dm_exec_sql_text provide information of each cached record and its cached query text accordingly.

USE msdb;
GO

SELECT OBJECT_NAME(q.[objectid]) AS module, c.objtype, q.[text], LEN(q.[text]) AS definition_length
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text (c.plan_handle) q
WHERE q.[text] LIKE '%dbo.sysjobs%'
OPTION (RECOMPILE)
GO

Hope these view and functions help to find list of stored procedures or functions that contains the table or some SQL syntax you are searching for.