Extracting stored procedure source text from SQL Server

It’s been a really long time since the last post so here’s a small SQL Server tip. I recently had to extract the source code for more than two hundred stored procedures from a SQL Server database. Obviously I didn’t want to do that by hand so I wrote a simple script using sp_helptext:

exec sp_helptext proc1
exec sp_helptext proc2
exec sp_helptext proc3
...

This did indeed extract all the source code for the procs but for some obscure reason I had a newline character after every 255:th character in the result. Close but no cigar…

I did some googling without finding anyone with the identical problem (I had already increased the “Maximum number of characters displayed in each column” setting of SQL Server Management Studio). However, it turns out there’s an alternative to sp_helptext, namely the sys.sql_modules table which can be queried like this:

select definition from sys.sql_modules where object_id = object_id('proc1')
select definition from sys.sql_modules where object_id = object_id('proc2')
select definition from sys.sql_modules where object_id = object_id('proc3')
...

This time there were no irritating newlines in the output. Problem solved!

/Emil