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 (http://msdn.microsoft.com/en-us/library/ms176090.aspx) 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 1 – OBJECT_DEFINITION

SELECT OBJECT_DEFINITION(OBJECT_ID('usp_StoredProcedureName'))

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] = sc.id
WHERE o.type_desc = 'SQL_STORED_PROCEDURE'
  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
WHERE OBJECT_DEFINITION([object_ID]) LIKE '%CATCH%'

Example 6 – OBJECT_DEFINITION to Script out Procedures

SET NOCOUNT ON;
DECLARE @strSQL NVARCHAR(MAX)
SET @strSQL = ''
SELECT @strSQL += OBJECT_DEFINITION([object_ID])+CHAR(10)+'GO'+CHAR(10)
FROM sys.procedures
SELECT @strSQL

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.