SQL Get Object Definition

There are several methods (sp_help and object_definition) for getting definitions for stored procedures and other procedural objects in SQL. One problem I’ve had with them is getting the definitions for very long routines. This seems to be due to the definition spanning more than one sys.syscomments rows.

This simple routine will return your object definition regardless of its length. I use a print as it isn’t limited to its result length.

Declare @def Varchar(max) = ''

Select @def = @def + coalesce(text, '')
FROM sys.syscomments
WHERE id = object_id('mystoredprocedurename')
Order By colid

print @def

About these ads

• dba
• definition
• development
• object
• object_definition
• sp_help
• sql
• stored function
• stored procedure