How to Get Definition for Stored Procedures, UDFs and Triggers using T-SQL

If you want to list the definition for a stored procedure, User Defined Function, or Trigger, you can use one of the following ways:

Alternative 1: sp_helptext (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using sp_helptext

sp_helptext ‘dbo.your_object_name’

[/sql]

Alternative 2: syscomments (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using syscomments

SELECT [text]
FROM sys.syscomments
WHERE OBJECT_NAME(id) = ‘your_object_name’

[/sql]

Alternative 3: OBJECT_DEFINITION (T-SQL)

[sql collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]
— using built in function OBJECT_DEFINITION

SELECT OBJECT_DEFINITION(OBJECT_ID(‘your_object_name’))

[/sql]

Advertisements

3 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s