How do we get the DEF of a view ?

Question

How do we get the DEF of a view ?

mw_sql_developer

SSCoach

Points: 19445
More actions
September 8, 2016 at 11:42 am

#329952

The code for a SP goes in this table. Similarly how can we get the DEFINITION of a view
select [definition] AS ProcDef from sys.sql_modules

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Sue_H SSC Guru Points: 90860 More actions · Answer 1

mw112009 (9/8/2016)
The code for a SP goes in this table. Similarly how can we get the DEFINITION of a view
select [definition] AS ProcDef from sys.sql_modules

USE YourDatabase

GO

SELECT OBJECT_NAME(object_id), definition

FROM sys.sql_modules

WHERE OBJECT_NAME(object_id) like 'YourViewName'

Sue

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

mw112009 (9/8/2016)
The code for a SP goes in this table. Similarly how can we get the DEFINITION of a view
select [definition] AS ProcDef from sys.sql_modules

Exactly the same way. Views, functions and stored procedures all have their definitions available from the sys.sql_modules catalog view

CREATE VIEW MyView

AS

SELECT 1 AS Col1;

GO

SELECT * FROM sys.sql_modules WHERE object_id = OBJECT_ID('MyView')

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alan Burstein SSC Guru Points: 61141 More actions · Answer 3

Alternatively, you can bypass sys.sql_modules all together and use OBJECT_DEFINITION.

CREATE VIEW MyView

AS

SELECT 1 AS Col1;

GO

SELECT OBJECT_DEFINITION(OBJECT_ID('MyView'))

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001