How to get a Stored Procedure's text in MS SQL

Saturday, January 20, 2007

MSSQL Server: How to get a Stored Procedure's text

If you're still struggling with databases and queries, and at one point you need to retrieve the text of some or all stored procedures automatically, you might wanna read this. It works for Microsoft SQL Server 2000 for sure, didn't test it on anything else.

These days I had to do a major update to a database, and it involved changing the way a common value was computed. For reports, the respective value was processed in stored procedures. There were a bunch of reports, so I needed a way of searching through the text of the stored procedures automatically for the pattern of the snippet that needed to change.

This is how you get the pieces of text that make a stored procedure:

select c.text, c.encrypted, c.number, xtype=convert(nchar(2), o.xtype), 
  datalength(c.text), convert(varbinary(8000), c.text), 0 
 from dbo.syscomments c, dbo.sysobjects o 
 where o.id = c.id and c.id = object_id(N'') 
 order by c.number, c.colid option(robust plan)

Of course, you can use a cursor and concatenate the the results to get the full text of the stored procedure. The most important information for me was that the text is kept in the syscomments system table.

This is not so interesting, unless you're facing a very similar problem, but a far better tip is how I got this query (no, I'm not that good at SQL).

I used the SQL Profiler that comes with the SQL Server Client Tools. While profiling, I opened the stored procedure in the Enterprise Manager. This is the query it uses for fetching the stored procedure text, with some further programmatic processing to get the neat display.

So, the main point is that if you want to do something a program is doing with the Sql Server but you cannot see how it's doing it otherwise, the profiler is a wonderful tool.

Of course, it serves it's actual purpose very well too. I used it to find the bottlenecks in an application that was very much database driven. Then I wrote a script that ran the critical pieces in a loop, and I used Optimization Wizard on the resulted trace to get some automatic indexes. The performance improvement was over 300%, enough in my case.

This is probably one of my last posts on SQL. It's such a "been there, done that" that I could get a lot better on, but don't really feel like it.

Posted by satov at 11:50 PM  

Labels: microsoft, sql, sql server, stored procedures, tips and tricks