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.
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 😉
There’s always one!!! 🙂
FYI: The SQL 2005/2008 replacement for syscomments is sys.server_sql_modules.
0 Pingbacks