3 Different Ways to display VIEW definition using SQL Server 2008 Management Studio

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:

View Definition SSMS 2008

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

OUTPUT

View Definition

Alternatively, you can also do a join between sys.views and sys.sql_modules to obtain the same result

at

9 comments:

  1. MadhivananMarch 15, 2010 at 8:00 AM

    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

    ReplyDelete
  2. Suprotim AgarwalMarch 16, 2010 at 2:14 AM

    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

    ReplyDelete
  3. Jeffrey WankMarch 22, 2010 at 2:38 AM

    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!

    ReplyDelete
  4. AnonymousJuly 6, 2010 at 6:31 AM

    Thanks guys, very useful posts!!

    ReplyDelete
  5. AnonymousSeptember 29, 2010 at 10:56 PM

    Very useful post. Keep up the good work guys... :)

    ReplyDelete
  6. Santanu.......March 23, 2011 at 12:10 PM

    This comment has been removed by the author.

    ReplyDelete
  7. Santanu.......March 23, 2011 at 12:18 PM

    A very handy post....!!

    ReplyDelete
  8. AnonymousDecember 18, 2012 at 11:22 AM

    Thank you, this helped a lot!

    ReplyDelete
  9. mikeJanuary 29, 2013 at 4:49 AM

    Here is how i do it:

    SELECT OBJECT_DEFINITION (OBJECT_ID(N'VIEW_NAME'));

    ReplyDelete