Display VIEW definition using T-SQL code (3 different ways)

Sometime back, I had posted about 3 Different Ways to display VIEW definition. Let us see some additional ways of displaying VIEW definition using T-SQL and some advantages/disadvantages of using these different methods. Thanks to Madhivanan on his inputs in the original posts.

I am using the AdventureWorks database:

Method 1: sp_helptext

sp_helptext 'Purchasing.vVendor'

Although this method is short, however there is no easy way (cursors required) to use the result of this query in another SQL query.

Method 2: sys.comments

SELECT sysco.text as [View Definition]
FROM sys.syscomments sysco
JOIN sys.objects sysob ON sysco.id = sysob.object_id
JOIN sys.schemas syssh ON sysob.schema_id = syssh.schema_id
WHERE sysob.name = 'vVendor'
AND syssh.name = 'Purchasing';

We get the same results as in sp_helptext, except here we can use the result of this query in another SQL query. The disadvantage here is that this method is lengthy and you have to specify the object name and schema name separately.

Method 3: OBJECT_DEFINITION

SELECT OBJECT_DEFINITION 
(OBJECT_ID(N'Purchasing.vVendor'))
as [View Definition]

This is one of my favorite methods as the result obtained from this method can be used in another query. Moreover the query is shorter.

OUTPUT (Same for all the three methods)

Note: It is also very easy to see the definitions of all VIEWS in your database using OBJECT_DEFINITION (as suggested by Madhivanan in the original post).

SELECT object_definition(object_id) as [View Definition]
FROM sys.objects
where type='V'

OUTPUT

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook