Find stored procedures, tables and functions in T-SQL

When developing a lot in database, it can be useful to search for database objects that contain (partly) a specific name. The stored procedure script below allows you to perform such a search.

/*
Find stored procedures / tables / functions
*/
CREATE procedure dbo.pr_FindIt   
(   
 @find varchar(128)   
)   
   
as   
    
SELECT    
 TABLE_SCHEMA AS OBJECT_SCHEMA,    
 TABLE_NAME AS OBJECTNAME,    
 TABLE_TYPE AS OBJECT_TYPE,   
 null as CREATED,   
 null as LAST_ALTERED,   
 null as SQL_DATA_ACCESS   
FROM INFORMATION_SCHEMA.TABLES    
WHERE TABLE_NAME LIKE '%' + @find + '%'    
   
union   
   
SELECT    
 ROUTINE_SCHEMA AS OBJECT_SCHEMA,    
 ROUTINE_NAME AS OBJECTNAME,    
 ROUTINE_TYPE AS OBJECT_TYPE,   
 CREATED,    
 LAST_ALTERED,    
 SQL_DATA_ACCESS    
FROM INFORMATION_SCHEMA.ROUTINES    
WHERE ROUTINE_NAME LIKE '%' + @find + '%'    
   
ORDER BY OBJECT_SCHEMA, OBJECT_TYPE, OBJECTNAME   
   
go

 

Other possibility is to use a simple query (example is for a stored procedure):

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%text_to_search_for%'
AND ROUTINE_TYPE='PROCEDURE'

 

Hope this helps,

Comments are closed