SQL Server 2017 Hosting :: How to Check Object Definition in SQL Server ?

Peter Cole | May 31, 2018 | SQL 2016 Hosting | No Comments

On this article, I am going to tell you about Check Object Definition in different way. sp_helptext is wide used for checking object definition in SQL Server. sp_helptext is accustomed check definition of varied database objects like Views, stored Procedures and User defined Functions.

There are 2 different choices which might be used to retrieve object definition:
OBJECT_DEFINITION( object_id ) – may be a built-in function. It also can retrieve definitions of CHECK/DEFAULT constraints
sys.sql_modules – may be a catalog view that returns definitions of all modules in current database

Each of those is used as follows:

USE [sqlhostingnews]
GO 
sp_helptext 'MyProcedure'
GO 
-- Use OBJECT_ID() function to get object id
SELECT    OBJECT_DEFINITION(OBJECT_ID('MyProcedure'))
GO

USE [sqlhostingnews]

sp_helptext 'MyProcedure'

-- Use OBJECT_ID() function to get object id

SELECT OBJECT_DEFINITION(OBJECT_ID('MyProcedure'))

-- Use OBJECT_ID() function to get object id
SELECT    [definition]
FROM    sys.sql_modules
WHERE    object_id = OBJECT_ID('MyProcedure')
GO

-- Use OBJECT_ID() function to get object id

SELECT [definition]

FROM sys.sql_modules

WHERE object_id = OBJECT_ID('MyProcedure')

OBJECT_DEFINITION(object_id) and sys.sql_modules will returns results as a oneline when in “Results to Grid” (Ctrl + D) mode. Next step, Switch to “Results to Text” (Ctrl + T) for formatted output which will include line breaks. Hope this tutorial works for you!

Related Posts

About The Author

Pete