CODEWITHSUNDEEP
sql-serversql-server-2008
Veejay
Veejay

Reputation: 242

Display view content in SQL Server2008

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?

Upvotes: 3

Views: 41637

Answers (3)

Gary Thomann
Gary Thomann

Reputation: 672

Adding this sqlcmd script for others as the 4k limit was not fun. Thanks to this thread.

-- :r meta_show_view_definition.sql
-- need user logon with ALTER permissions
-- :setvar ObjectName "<string>"
set nocount on

declare @sql varchar(max)
:out $(ObjectName).view.dump.sql

set @sql = '
select obj.name, sm.definition
from   sys.objects obj
join   sys.sql_modules sm  on  obj.object_id = sm.object_id
where
    obj.type = "V" and
    upper(obj.name) = upper("' + '$(ObjectName)' + '")' 

exec (@sql)
go

:out stdout
set nocount off

Upvotes: 0

Thomas
Thomas

Reputation: 64645

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

Upvotes: 5

gbn
gbn

Reputation: 432261

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

Upvotes: 6

Related Questions