OBJECT_DEFINITION (Transact-SQL)

How to view the definition of a module, is one of the activities that we need very frequently whether it is customization or debugging in SQL Server. Today, I was debugging one of the stored procedures, so thought of writing this article.
There are multiple ways to view the definition of a module. I will mention two of them here and you can choose either of them.


The SQL Server Database Engine assumes that object_id is in the current database context. The collation of the object definition always matches that of the calling database context.
OBJECT_DEFINITION applies to the following object types:

• 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
  

METHOD 1 :
Given below is the solution using OBJECT_DEFINITION (One of the built-In metadata functions in SQL Server).

1 2 3 4 5 6 7 8

USE AdventureWorks2012 -- Donot forget to change database name here GO SELECT OBJECT_DEFINITION (OBJECT_ID('[HumanResources].[vEmployee]')) -- Donot forget to change schema and table name -- as highlighed above in blue color. AS ObjectDefinition; GO --OUTPUT

METHOD 2 :
Given below is the solution using sys.sql_modules (One of the object catalog views in SQL Server).

1 2 3 4 5 6 7 8 9

USE AdventureWorks2012 -- Donot forget to change database name here GO SELECT definition FROM sys.sql_modules WHERE object_id = OBJECT_ID('HumanResources.dEmployee'); -- Donot forget to change schema and table name -- as highlighed above in blue color. GO --OUTPUT

CONCLUSION :
As you can see, both methods give you the same result set. However, I personally use Method 1 due its less number of codes. Let me know which method you prefer ?
Read Full Post »