Skip to content

So then, what’s the definition of an object……..?

January 25, 2012

Not blogged for a while due to client and project commitments but something which has surprised me when speaking with colleagues both past and present is that when I mention the built in function OBJECT_DEFINITION, the majority of DBA’s haven’t heard of it, never mind used it. So i felt it necessary to dust off the blog typing fingers and see if i can enlighten :)

So, I though it be a good idea to enlighten a few people to how it can be used by giving real world examples.

Firstly, a short definition (no pun intended) from BOL ( as to what exactly this function does:

Returns the Transact-SQL source text of the definition of a specified object.

Its as simple as that!

Pass in the Object_ID which it expects to be in the current database context and it spits out the text. I’ll show you a couple of examples of how it works in comparison to how I’ve seen the same thing done but by using sp_helptext as well as some of the other system tables.

I’ll not beat around the bush and get straight into a few examples and old skool alternatives as there’s not really much more i can say about the function itself:



Example 2 – sp_helptext

EXEC sp_helptext 'usp_StoredProcedureName'

Example 3 – Using system tables to search (this is a common way I’ve seen this done)

SELECT  o.[name]
	  , o.type_desc
	  , sc.[text]
FROM  sys.objects o
INNER JOIN syscomments sc ON o.[object_id] =
	  AND o.[name]  = 'usp_StoredProcedureName'

Example 4 – OBJECT_DEFINITION for multiple objects

SELECT [object_ID], [Name], OBJECT_DEFINITION([object_ID]) AS ProcText
FROM sys.procedures

Example 5 – OBJECT_DEFINITION for multiple with filtering

SELECT [object_ID], [Name],  OBJECT_DEFINITION([object_ID]) AS ProcText
FROM sys.procedures

Example 6 – OBJECT_DEFINITION to Script out Procedures



SET @strSQL = ''
FROM sys.procedures


Now this can be used for all programmability objects within SQL Server, not just procedures so the same works for Views, functions, triggers etc

Again from BOL here is a full list:

C = Check constraint

D = Default (constraint or stand-alone)

P = SQL stored procedure

FN = SQL scalar function

R = Rule

RF = Replication filter procedure

TR = SQL trigger (schema-scoped DML trigger, or DDL trigger at either the database or server scope)

IF = SQL inline table-valued function

TF = SQL table-valued function

V = View

So there you have it, short n snappy blog today and I really hope that it helps give people a new insight into how to get object text.

  1. Great job but I’m gonna have to pick you up on coding standards, keywords in lower case and reserved words not enclosed in [] tut tut tut ;)

  2. There’s always one!!! :)

  3. David Lathrop permalink

    FYI: The SQL 2005/2008 replacement for syscomments is sys.server_sql_modules.

Leave a Reply

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

You are commenting using your 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


Get every new post delivered to your Inbox.

%d bloggers like this: