|
|
Here are 3 different ways to display VIEW definitions:
Method 1: Use sp_helptext
USE Northwind
GO
EXEC sp_helptext Invoices
Method 2: Use SQL Server 2008 Management Studio
Open SSMS 2008. Choose the database and expand the ‘Views’ node. Right click on it > Script View as > CREATE To > Choose different options as shown below:
Method 3: If you want to display the name and definitions of all view in a database, use this query:
SELECT TABLE_NAME as ViewName,
VIEW_DEFINITION as ViewDefinition
FROM INFORMATION_SCHEMA.Views
Alternatively, you can also do a join between sys.views and sys.sql_modules to obtain the same result
Did you like this post?
|
|
||
|
|
|
|
|
subscribe via rss |
|
subscribe via e-mail |
|
print this post |
|
follow me on twitter |
comments
7 Responses to "3 Different Ways to display VIEW definition using SQL Server 2008 Management Studio"Note that information_schema.views will show you only 4000 characters
Other reliable methods are
1
select object_definition(object_id) from sys.objects
where type='v'
2
select * from sys.[sql_modules]
where definition like 'CREATE VIEW%'
Madhivanan
http://beyondrelational.com/blogs/madhivanan
Thanks Madhivannan. As mentioned in my post, making a JOIN (using objectid) between sys.views and sys.sql_modules will get the View Name and View Definition
Thank you Madhivaan and Suprotim for sharing your solutions. This is very useful as I used to manually check views from management studio. Knowledge does wonders!
Thanks guys, very useful posts!!
Very useful post. Keep up the good work guys... :)
A very handy post....!!
Post a Comment