Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database

How to find a specific string from stored procedures, triggers and functions in database

5.00/5 (6 votes)
4 Feb 2012CPOL 60.1K  
You can find queries to find a specific string from MSSQL Server
There are several tables and views containing stored procedures, functions and triggers definitions.

Views –
SQL
SYS.SYSCOMMENTS, SYS.SQL_MODULES


Tables –
SQL
INFORMATION_SCHEMA.ROUTINES


I recommend the following query. It shows all SPs and Fns that contain any specific text. If we want to search only procedures, then filter in where clause "AND obj.Type='P'".

SQL
SELECT DISTINCT obj.name AS Object_Name,obj.type_desc 
FROM sys.sql_modules sm INNER JOIN sys.objects obj ON sm.object_id=obj.object_id 
WHERE sm.definition Like '%search text%'


If we want to search specific function or objects names, we can find dependencies using the following system stored procedure:
SQL
EXEC sp_depends @objname =searchobjectname


There is another way to find specific text using INFORMATION_SCHEMA.ROUTINES. But for me, it didn’t give me all the results.
SQL
SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%search text%'
--AND ROUTINE_TYPE='PROCEDURE' (for procedures only)


And also, SYS.SYSCOMMENTS view can have the same usage, but I got duplicates from some results.

SQL
SELECT SO.NAME, SC.TEXT
FROM SYS.SYSOBJECTS SO
JOIN SYS.SYSCOMMENTS SC ON SO.ID = SC.ID
WHERE SC.TEXT LIKE '%search text%' 
–-AND SO.TYPE = 'P'(for procedures only)


Hope this post helps you. Feel free to give your suggestions on this post. Until next post, cheers!!!

[edit]"Treat my content as plain text..." option disabled - OriginalGriff[/edit]

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)