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

image

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

image


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

0 Responses to "Display VIEW definition using T-SQL code (3 different ways)"
 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions