Sql Server - Get view creation statement for existing view

Question

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.

score 22 · Accepted Answer · edited Jun 21 '19 at 00:55

22

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

edited Jun 21 '19 at 00:55

DJDaveMark

2,669
23
35

answered Dec 07 '09 at 19:27

Adriaan Stander

162,879
31
289
284

The result is splitted in different rows: how to have the actual content in a single string to be used to generate a single sql statement? – ff8mania May 06 '15 at 17:50
Full syntax is: `sp_helptext [ @objname = ] 'name' [ , [ @columnname = ] computed_column_name ]` from [the official MS docs](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-helptext-transact-sql). – DJDaveMark Jun 20 '19 at 20:15

score 8 · Answer 2 · answered Dec 07 '09 at 19:26

8

Right click on the view and script it.

answered Dec 07 '09 at 19:26

HLGEM

94,695
15
113
186

score 7 · Answer 3 · answered Dec 07 '09 at 19:27

7

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

answered Dec 07 '09 at 19:27

Remus Rusanu

288,378
40
442
569

Tim-Charlotte · Answer 4 · 2013-10-31T18:39:22.873

3

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.

edited Oct 31 '13 at 18:39

answered Oct 31 '13 at 18:33

Tim-Charlotte

31
2

score 3 · Answer 5 · edited Aug 14 '17 at 07:16

3

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.

edited Aug 14 '17 at 07:16

TT.

15,774
6
47
88

answered Feb 18 '15 at 21:26

Tom McCarthy

31
1

This worked perfectly, for newbies just put the name of your view in the middle of %% . – Andres Ramos May 10 '18 at 04:58

score 1 · Answer 6 · answered Nov 20 '20 at 14:15

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

score 0 · Answer 7 · answered Jan 09 '23 at 14:52

0

USER needs ViewDefinition rights to view Definition column (SQL Create Statement)

answered Jan 09 '23 at 14:52

Kenneth Gardzinski

1

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 10 '23 at 20:52

Sql Server - Get view creation statement for existing view

7 Answers7

Linked