As I sat at my desk, like most days, pondering what a partial database restore truly is in regards to object-level restores and filegroups and noting that though this sentence will go on longer than I intended I will not finish it prior to finishing the initial thought, my phone rang and the question came to the DBA, me, "How can I search for code in a view or stored procedure that contains a specific string?".

Here I ponder weak and weary...

It should have been obvious...the answer should have come immediately, however,with a bit of time to spare, I explained I would return the call momentarily as I had another critical issue to deal with.

So, how does one do what was asked in the first long sentence of this entry?

I knew that the "sysobjects" table contained the objects themselves: views, stored procedures, tables, etc. But where was the actual code that defined these objects?  Then it came to me:

sp_helptext

This little gem of a system stored procedure will return the code for which my interogator wanted to search. So where does sp_helptext get its info? Again, simple:

try:

sp_helptext sp_helptext

In the bowels of the sp_helptext stored procedure itself was revealed the missing table:

syscomments

So the query issued forth that would expose the TEXT field which holds the answer. A join from sysobjetcs to syscomments on the ID column was all that was required.

Throw in criteria for the XTYPE field, and you can narrow your search to a View(V) or Stored Procedure (P).

Fianlly, the impetus for a Reporting Services report was born. One that can pass in a parameter as the object name with other parameters for database and even server that would control the data source itself via an expression. One would only have to run the report, pass in the search string and the report would show not only the objects that contained the string, but the code too. This report, like the cat in the box or the winning numbers of a lottery ticket do not exist yet. But...

I will make this report if there is interest in it. I will make it and I will distribute the RDL for all who have read this far and agree there is a need. I can even have it posted by this Friday. I work well under deadline.

Rodney

Author "Pro SQL Server 2005 Reporting Services"