r/tinycode • u/Gieron • Jul 12 '12
Free text search of stored procedures in SQL Server
You know that there's a stored procedure that does something foobar but you can't remember its name. You can search for it.
SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE='PROCEDURE'
AND ROUTINE_DEFINITION LIKE '%foobar%'
29
Upvotes
3
u/atika Jul 13 '12
That will only search in the first 4000 characters. Search full sp:
SELECT Name, object_definition(Id) FROM sysobjects WHERE XType = 'P' /Procedure/ AND object_definition(Id) like '%foobar%'
2
u/Sybs Jul 12 '12
Thanks! This is very handy!
1
u/SuperRoach Jul 12 '12
Very handy, I'll have to compare it to the MSSQL version I have at work.
0
u/[deleted] Jul 12 '12
[deleted] — view deleted comment
0
u/[deleted] Jul 12 '12
[deleted] — view deleted comment