SQL Server Portal

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.

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

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

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 ?