Sql Server 2005/2008 Object Definition – Getting all the contents of a stored procedure etc…

I thought you had to go to the sys.syscomments table to do this:

SELECT * FROM sys.procedures
WHERE [name] Like ‘GetAllElephants’
ORDER BY [name]

The above would return the stored procedure with the name ‘GetAllElephants’. one of the columns will contain the ID (object_id column) which you then pass to the OBJECT_DEFINTION function as shown below!

SELECT OBJECT_DEFINITION(1409815502);

This will then return all of the contents of the stored procedure … that is the actual code that IS the stored procedure.

Or you could just do this:

SELECT OBJECT_DEFINITION(OBJECT_ID(N’GetAllElephants’));

You can do this with

  • Stored Procedures
  • Views
  • Table Valued Functions
  • Scarlar Valued functions
  • Triggers
  • Inline table Valued Functions
  • and few other constraints and replication related items.. (see documentation below)

OBJECT_DEFINITION Documentation

Sql Server 2005

Sql Server 2008

Advertisements

One Response to “Sql Server 2005/2008 Object Definition – Getting all the contents of a stored procedure etc…”

  1. Geraldine Caszo Says:

    It does not work. It does not return the entire text of the procedure


Leave a Reply

Please log in using one of these methods to post your comment:

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

%d bloggers like this: