ansaurus

Question

Display view content in SQL Server2008

Answer 1

+2 A:

The simplest way using Management Studio is to:

Find the database in Management Studio
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.
Select all your views.
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

Thomas 2010-03-12 05:42:01

Thanks..can you share T-SQL script to display all view definition at once?

Veejay 2010-03-12 05:45:31

Done. You can query for them via the INFORMATION_SCHEMA.VIEWS

Thomas 2010-03-12 05:51:51

which is better? sys.views or INFORMATION_SCHEMA.VIEWS?

Veejay 2010-03-12 06:01:33

sys.views is proprietary to SQL Server. The INFORMATION_SCHEMA views are supposed to be somewhat universal and thus if given the choice would be the preferred method. However, it really comes down to preference. IMO I find the INFORMATION_SCHEMA views to simpler to read.

Thomas 2010-03-12 06:05:17

It's a shame INFORMATION_SCHEMA will give wrong data..

gbn 2010-03-12 06:45:13

@gbn Not true. If the view def is over 4K you will get a null instead. It will not truncate.

Thomas 2010-03-12 07:52:02

I will say that when you script a view in SMS, it uses a combination of various sys views (although not sys.views specifically) which I'm guessing are variants of what you'll get from sys.views.

Thomas 2010-03-12 07:57:34

NULL or truncate is equally bad... no?

gbn 2010-03-12 07:58:40

@gbn. Perhaps not equally bad but still definitely undesired I grant you.

Thomas 2010-03-12 14:59:22

Answer 2

+1 A:

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

gbn 2010-03-12 06:44:45

Not true. According the BOL: "If the length of definition is larger than nvarchar(4000), this column is NULL. Otherwise, this column is the view definition text." So you should never get truncation. If you have views over 4k that is another matter.

Thomas 2010-03-12 07:48:32

ansaurus

tags:

views:

answers:

Display view content in SQL Server2008

related questions