CODEWITHSUNDEEP
sql-server
rosscj2533
rosscj2533

Reputation: 9323

Sql Server - Get view creation statement for existing view

Is there a way to get the statement that created a view for an existing view in SQL Server 2008? I thought there was a stored procedure or some metadata that had this data, but I'm not finding anything.

Upvotes: 10

Views: 52264

Answers (6)

Alain
Alain

Reputation: 27250

In case it helps someone in the future, here's a little script I put together to output the creation script for all views in a database.

DECLARE @definition varchar(max)
DECLARE @view CURSOR
SET @view = CURSOR FOR
    SELECT m.definition FROM sys.views v INNER JOIN sys.sql_modules m ON m.object_id = v.object_id
OPEN @view
FETCH NEXT FROM @view INTO @definition
WHILE @@FETCH_STATUS = 0 BEGIN
    PRINT @definition
    PRINT 'GO'
    FETCH NEXT FROM @view INTO @definition
END CLOSE @view DEALLOCATE @view

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166606

Have you had a look at sp_helptext?

sp_helptext 'dbo.name_of_view'

SQL SERVER – Stored Procedure to display code (text) of Stored Procedure, Trigger, View or Object

Upvotes: 23

Tom McCarthy
Tom McCarthy

Reputation: 39

You can see the script as code, and copy paste it into an editor like this:

SELECT 
    v.TABLE_NAME, 
    v.VIEW_DEFINITION 
FROM 
    INFORMATION_SCHEMA.VIEWS v 
WHERE 
    v.TABLE_NAME LIKE '%%'

and insert the view name you want.

Upvotes: 3

Tim-Charlotte
Tim-Charlotte

Reputation: 31

Right click on the View name, then left click "SCRIPT VIEW as", then left click "ALTER TO", then left click "NEW QUERY EDITOR WINDOW" -- bingo, your there! To print, click on the script screen and then send to your printer using your toolbar printer icon or click on FILE>>PRINT. Of course, be careful to exit without making any changes. This works the same for stored procedures.

Upvotes: 3

Remus Rusanu
Remus Rusanu

Reputation: 294467

It's in sys.sql_modules. Other schema tables like INFORMATION_SCHEMA ones only contain the first 4000 characters of the definition (they truncate).

Upvotes: 7

HLGEM
HLGEM

Reputation: 96650

Right click on the view and script it.

Upvotes: 8

Related Questions