SQL Server Tip: Search within Stored Procedures, Functions and Views

Often during development and maintenance of applications, the biggest issue for the developer is searching dependent objects in SQL server. These are generally the stored procedures, views and functions. The SQL server stores these procedures, views and functions in Tables. We just have to search the right tables for results.

Searching Stored Procedures, user defined Functions and Views

The below T-SQL query is used for searching stored procedures and function for a specific keyword.

SELECT Tsu.name AS schemaname, Tso.name AS routinename, Tsc.[text] AS routine, Tso.xtype AS routinetype
FROM sys.sysobjects AS Tso INNER JOIN sys.syscomments AS Tsc ON Tso.id = Tsc.id 
INNER JOIN sys.sysusers AS Tsu ON Tso.uid = Tsu.uid
WHERE     (Tso.xtype = 'P' OR Tso.xtype = 'v' OR Tso.xtype = 'FN') AND (Tsc.text LIKE '%Search Keyword%')
ORDER BY routinetype, routinename

Replace the “Search Keyword” by your search keyword. For e.g. “EMPLOYEE” which can be a table name. The query will list all the stored procedures and user functions that have the keyword in it.

SELECT Tsu.name AS schemaname, Tso.name AS routinename, Tsc.[text] AS routine, Tso.xtype AS routinetype
FROM sys.sysobjects AS Tso INNER JOIN sys.syscomments AS Tsc ON Tso.id = Tsc.id 
INNER JOIN sys.sysusers AS Tsu ON Tso.uid = Tsu.uid
WHERE     (Tso.xtype = 'P' OR Tso.xtype = 'v' OR Tso.xtype = 'FN') AND (Tsc.text LIKE '%EMPLOYEE%')
ORDER BY routinetype, routinename

Searching Views for references to table names and column names and vice versa.

Just check out this cool TSQL Queries. Experiment with the View 'INFORMATIONSCHEMA.VIEW_COLUMN_USAGE'.

-- View All Info
select * from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

-- List Table and columns referred by View "Orders Qry"
select DISTINCT VIEW_CATALOG, TABLE_NAME, COLUMN_NAME 
 from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE 
  WHERE VIEW_NAME = 'Orders Qry'

-- List Views which referres Table "Customers"
select DISTINCT VIEW_SCHEMA, VIEW_NAME 
 from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE 
  WHERE TABLE_NAME = 'Customers'

-- List Views that referrer Table "Products" and it's Column "CategoryID"
select DISTINCT VIEW_SCHEMA, VIEW_NAME from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE 
 WHERE TABLE_NAME = 'Products' AND COLUMN_NAME = 'CategoryID'