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
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
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]