By: Tim Ford | Last Updated: 2019-05-03 | Comments (5) | Dynamic Management Views and Functions
How many times have you had to troubleshoot or make changes to an existing database that is not documented properly or completely? Even need to look for a specific stored procedure that references a specific table or process? If you have intimate knowledge of the database then this may not be much of an issue for you. What happens if this database is from an external developer, a turn-key solution provider, or developed by another individual within your company? If you utilize the INFORMATION_SCHEMA.ROUTINES view to research and troubleshoot this issue, the task goes from daunting to a cake-walk.
The INFORMATION_SCHEMA.ROUTINES view, introduced in SQL 2000 exposes metadata in the current database as it pertains to content derived from the syscomments and sysobjects system views and system tables. It contains one row for each function and stored procedure in the database for which the current user has rights. This is an important note to make. If you have limited rights to the database, INFORMATION_SCHEMA.ROUTINES may not return a complete set of results as it pertains to the query you are executing.
If you are searching for a specific keyword or block of text you can simply query the INFORMATION_SCHEMA.ROUTINES view as follows, substituting your search string for "KEYWORD":
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%KEYWORD%' AND ROUTINE_TYPE='PROCEDURE' ORDER BY ROUTINE_NAME
This is the equivalent of running the following query directly against the system tables/views:
SELECT sys.sysobjects.name, sys.syscomments.text FROM sys.sysobjects INNER JOIN syscomments ON sys.sysobjects.id = sys.syscomments.id WHERE sys.syscomments.text LIKE '%KEYWORD%' AND sys.sysobjects.type = 'P' ORDER BY sys.sysobjects.NAME
The INFORMATION_SCHEMA.ROUTINES view can be used to search for content of functions as well. Simply alter your WHERE clause as follows, substituting your search string in place of "KEYWORD" once again:
SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%KEYWORD%' AND ROUTINE_TYPE='FUNCTION' ORDER BY ROUTINE_NAME
To highlight the fact that this process is backward-compatible to Microsoft SQL Server 2000, I've run the following query against the Northwind database. I'm interested in finding all the stored procedures that utilize the ROUND() function:
USE Northwind GO SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%ROUND%' AND ROUTINE_TYPE='PROCEDURE' ORDER BY ROUTINE_NAME GO
From this I am able to determine that there are two stored procedures utilizing that function.
Here is another example to find code that contains a string. This is an example provided by one of our readers. With this code you can specify the type of object you want to search:
- TR - trigger
- FN - scalar function
- IF - table valued function
- V - view
- P - procedure
DECLARE @ObjectType VARCHAR(25)= 'P'; -- TR, FN, IF, V, P DECLARE @Code VARCHAR(25)= 'customer'; SELECT s.name + '.' + o.name, m.definition FROM sys.sql_modules AS m INNER JOIN sys.objects AS o ON m.object_id = o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id WHERE o.type = @ObjectType AND o.name NOT LIKE 'sp_%' AND m.definition LIKE '%' + @Code + '%' ORDER BY s.name , o.name;
- The next time you need to troubleshoot an object, consider exploring the INFORMATION_SCHEMA views. We've been told since SQL Server 2000 not to drill into the system tables, yet we all do - we are comfortable with doing so after eight years. Do not let fear of the unknown stop you from simplifying your metadata querying by making use of the INFORMATION_SCHEMA views, Dynamic Management Views and Dynamic Management Functions.
- Stay tuned for more INFORMATION_SCHEMA, DMV, and DMF tips. Until then,
check out these tips:
- Dynamic Management Views and Functions - SQL Server 2005 Exposed
- SQL Server 2000 to 2005 Crosswalk - Code Identification
- SQL Server 2000 to 2005 Crosswalk - Locking Resources
- Finding process percentage complete with dynamic management views
- SQL Server 2005 Resource Database Values in Dynamic Management Views\Functions
Last Updated: 2019-05-03
About the author
View all my tips