Display view content in SQL Server2008

Question

How can I display the content of all Views in a db in SQL Server 2008? Is there any new system views to do the same?

Answer 1

SELECT
    O.name, SM.definition
FROM
    sys.objects O
    JOIN
    sys.sql_modules SM ON o.object_id = SM.object_id
WHERE
    o.type = 'V'

"INFORMATION_SCHEMA" has a column limit of nvarchar(4000) which will cause truncation of the view definition when you select it: don't use it

Answer 2

The simplest way using Management Studio is to:

1. Find the database in Management Studio
2. In the database, click on the Views folder on the left (officially called the Object Explorer) which should show you a list of the views on your right. If it doesn't, you want to go to the View menu and chose Object Details.
3. Select all your views.
4. Right-click on the selected views and choose "Script View As" -> Create To -> New Query Window

This will open a window with the View definition of everything you selected.

EDIT: If you want to query for all your view definitions can you do something like:

Select VIEW_DEFINITION
From INFORMATION_SCHEMA.VIEWS

If you change your output to Text instead of Grid, it will given you a listing of all your views.

As gbn pointed out, if it's the schema you want and if some developer is crazy enough to make a view with more than 4k characters, the INFORMATION_SCHEMA views will return null for that view's schema. So, in that case using the system tables would be more appropriate. A variation of gbn's solution which is akin to what SMS does behind the scenes would be:

Select smv.definition
FROM sys.all_views AS v
    JOIN sys.sql_modules AS smv 
        ON smv.object_id = v.object_id