How to extract our stored procedure code/text definition on MS SQL Server

I had a very interesting situation last week when visited one of my customer. Situation was they host their application on a server at their US headquarter and it run a SQL Server 2005, while all developer engine at Jakarta using SQL 2008. We got an issue on application which resulted on wrong calculation, after some time analyze I found we need to investigate the stored procedure in used. But, big concern was none of developer have latest version of the stored procedure. Thus, we need to extract it from US Server.  It got weird when we able to connect to US Server but unable to iterate SQL Server 2005 entity though we logged in as database owner. Due to limited time, so I asked the developer to run an ad-hoc command to extract stored procedure definition from US Server. Response I got was “how to do so ?”


For some developer who already spoiled by luxury of Microsoft Tools, most of the time, ad-hoc command become a history, thus how SQL Server works and how to manage it on console mode become another legend.


On every SQL Server Database, system will create system table called sysobjects to store all objects within respective database, this table will store information about objectid, object name, object type, etc. Respective to that object, there is syscomments table to maintain comment or text definition of specific object, it was referenced to sysobject through … yes, objectid field.


Now how to get code definition of a stored procedure:

Traditionally we can use below command

select text 
from syscomments c
inner join sysobjects o on c.id=o.id
where o.name like 'your_sp_name'

But when it gave a big text, result will be truncated so it would be difficult for us to read it. Nah, there is a ad-hoc command already prepared by SQL Server to perform such task, it was called sp_helptext


So what we need to, is just type below command on console

sp_helptext 'your_sp_name'

And voilla, all the magic to extract code/text definition of stored procedure will be taken care with easy.