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
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.