Search for Code in Stored Procedures

One handy trick that I used to do was use the function OBJECT_DEFINITION() to view the code of a sproc. It’s a handy little function that doesn’t get much love as OBJECT_ID or OBJECT_NAME(). Let’s say we want to get the code of the sproc dbo.uspGetBillOfMaterials from DB AdventureWorks. We do like so:

SELECT OBJECT_DEFINITION( OBJECT_ID('dbo.uspGetBillOfMaterials') )

You get a result that may not be too pretty. In this case, use PRINT rather than SELECT, since SSMS strips out line breaks to squeeze it in the cell.

Now let’s search all available user sprocs in the database to search for the string “%GetBillOf% :

SELECT  OBJECT_DEFINITION( p.[object_id] ) AS code 
FROM    sys.procedures p
WHERE   OBJECT_DEFINITION( p.[object_id] ) LIKE '%GetBillOf%'

Now, I do longer search this way since every day I have a job that writes out all DB objects (tables, sprocs, etc.) to a individual files and they get indexed via Copernic Desktop Search. I script them out using SQL Compare.

Unfortunately, I don’t know why, OBJECT_DEFINITION() doesn’t work on tables. It’s been a wanted feature by the community. If you want to learn more about this function, check out:

Leave a Reply