SQL Server Performance

Scripting out all Views in a database - easy

Discussion in 'General Developer Questions' started by eramgarden, Aug 1, 2006.

  1. eramgarden New Member

    I have 195 Views in my database. I want to script out the text of the views and run them on another database to create them.

    Found this link:
    http://www.aspfaq.com/show.asp?id=2526

    and at the bottom it has :


    SELECT
    Definition = [text]
    FROM
    syscomments
    WHERE
    OBJECTPROPERTY(id, 'IsMsShipped') = 0
    AND OBJECTPROPERTY(id, 'IsView') = 1
    ORDER BY
    OBJECT_NAME(id)



    I do get a script of Views but they're chopped off! For example, I get :
    create View [viewname] select
    [column1],
    [column2],
    Create View [viewname1] select
    column1,
    [colu

    with column names and FROM missing Not sure what that's happening but any help would be great.
  2. Madhivanan Moderator

    Did you execute it QA?

    Madhivanan

    Failing to plan is Planning to fail
  3. joechang New Member

    this is query analyzer default of 256 char per column,
    go to QA -> tools -> options -> results
  4. Adriaan New Member

    Another thing is that the TEXT column of syscomments has a misleading name: it is actually NVARCHAR(4000).

    If the definition is longer than 4000 characters, there is at least one other row in syscomments - check the colid column.
  5. Adriaan New Member

    ... and of course you can use Enterprise Manager to do it for you much more easily ...
  6. eramgarden New Member


    ohhhhh, now it's working. I didnt know about 256 thingy..

    one more question: That script creates the views but there's no "GO" between each "create view" statement and I get an error that :

    Incorrect syntax near the keyword 'Create'.

    'CREATE VIEW' must be the first statement in a query batch.

    anyway to put "GO" between each "create view" statement" ?
  7. eramgarden New Member


    Silly me, I can "SELECT Definition = [text] + ' GO' "

    but "GO" needs to be on the next line..i need a carriage return i think
  8. eramgarden New Member



    Ohhhhhh, silly me again, yes, I can use the Enterprise Manager!

    I feel so stupid now [:I]
  9. Adriaan New Member

    And did you notice my other post, about the column size of TEXT?
  10. eramgarden New Member


    Just saw it but not sure what u mean..

    do u mean to change that script to look at colid as well??
  11. aefager New Member

    quote:Originally posted by eramgarden


    Silly me, I can "SELECT Definition = [text] + ' GO' "

    but "GO" needs to be on the next line..i need a carriage return i think
    When you need to include a carriage reutrn within the replace string, it might be easier to use Word, so that "^pCREATE VIEW" gets replaced with "^pGO^p^pCREATE VIEW". I know you already found a solution through Enterprise Manager, but just commenting that Word becomes a much better editor to use when dealing with special characters.
  12. Adriaan New Member

    quote:Originally posted by eramgarden


    Just saw it but not sure what u mean..

    do u mean to change that script to look at colid as well??

    The problem is that if the script is longer than 4000 characters, there are multiple rows in syscomments for the same ID value, but with an increasing COLID value.

    The script is also broken off abruptly at 4000 characters, so it might occur in the middle of a keyword or an object reference, and you can't just add ' GO'.

    Using EM - or a 3rd party tool - is a much better idea. Don't forget to script out permissions too.
  13. LearnSqlServer.com New Member

    I would agree with Adriaan about using a 3rd party tool or EM but I have an additional suggestion: why don't you just run this in DTS and output to a text file? This way, you aren't limited by the limits imposed by Query Analyzer.

    As for SQL Server's system tables having the increasing COLID value, if I'm not mistaken, the INFORMATION_SCHEMA.VIEWS view brings the entire definition back regardless of how many characters the view definition is.


    USE tempdb
    SELECT 'SELECT ''' + REPLICATE ('a', 5000) + ''''
    GO
    CREATE VIEW TestView AS <insert the generated SELECT from above>'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
    AS Mycolumn
    GO
    SELECT * FROM TestView

    sp_helptext TestView

    SELECT * FROM INFORMATION_SCHEMA.VIEWS
    Both sp_helptext and INFORMATION_SCHEMA.VIEWS show the full amount

    /*******************************

    Scott Whigham

    Check outhttp://www.LearnSqlServer.com/VideoTutorials/ - SQL Server 2005 and 2000 Tutorials

    *******************************/
  14. FrankKalis Moderator

    quote:
    Both sp_helptext and INFORMATION_SCHEMA.VIEWS show the full amount
    You're sure?
    The INFORMATION_SCHEMA.VIEWs view is restricted to 4,000 characters and should rather display NULL in the VIEW_DEFINITION column when the actual SQL statement is longer.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
  15. LearnSqlServer.com New Member

    Sorry for the long aaaaaaaaaaaaa post above. I tried deleteing the aaaaaaaaaaaaaaaaaaaa's but it won't let me - some bug in the Rich Text editor, I suppose.

    /*******************************

    Scott Whigham

    Check outhttp://www.LearnSqlServer.com/VideoTutorials/ - SQL Server 2005 and 2000 Tutorials

    *******************************/
  16. LearnSqlServer.com New Member

    quote:Originally posted by FrankKalis


    quote:
    Both sp_helptext and INFORMATION_SCHEMA.VIEWS show the full amount
    You're sure?
    The INFORMATION_SCHEMA.VIEWs view is restricted to 4,000 characters and should rather display NULL in the VIEW_DEFINITION column when the actual SQL statement is longer.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster:http://www.insidesql.de
    Heute schon gebloggt?http://www.insidesql.de/blogs
    Hard to test but, when I'm in 2005, I see all of it with sp_helptext but INFORMATION_SCHEMA.VIEWS shows the ellipsis. If that's what the docs show, then I'd go with the docs!

    /*******************************

    Scott Whigham

    Check outhttp://www.LearnSqlServer.com/VideoTutorials/ - SQL Server 2005 and 2000 Tutorials

    *******************************/

Share This Page