How to quickly get stored procedure text
I've answered this question twice today so I thought it made good sense to blog it. I am going to give three examples that will quickly get you to the text of a stored procedure or other object type. I am assuming you are looking to skip the [Database Name] --> Programmability --> Stored Procedures path of Object Browser, although the new "DROP and CREATE to" option in SQL 2008 is nice. We will be using the following methods today:
- OBJECT_DEFINITION()
- sys.sql_modules
- sp_helptext
First up is OBJECT_DEFINITION(). This system function takes an object_id and returns a varchar(max) output of the object source text. There are multiple types that can be used with OBJECT_DEFINITION, check the BOL reference for a complete list. The syntax for this function is:
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.GetCustomer', 'P')) AS objectText;
Next up is the object catalog view sys.sql_modules. This view returns a row for each object that is a SQL language-defined module. Again, check the BOL reference for your complete list of types. This view returns many useful pieces of information but today we are only after the definition column, which contains the SQL text of the module. Here's the simple query:
SELECT [definition] AS objectText
FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID(N'dbo.GetCustomer', 'P');
Last is the old standby sp_helptext. This system stored procedure takes and object name (and potentially a column name) and returns the definition of multiple object types with a 255 character row output. The source for this stored procedure is sys.sql_modules, referenced above. Here is a sample call to this stored procedure:
EXECUTE sp_helptext @objName = N'dbo.GetCustomer';
The one additional option provided by sp_helptext is the ability to return computed column definitions. To get that detail we simply add the columnname parameter:
EXECUTE sp_helptext @objName = N'dbo.Customer', @columnname = 'CustomerComputedColumn';
So the next time you have that "what does this object do?" moment give one of these options a try.