September 21, 2010

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 = sysob.object_id
JOIN sys.schemas syssh ON sysob.schema_id = syssh.schema_id
WHERE = 'vVendor'
AND = '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.


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'



About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

No comments: