SQL Server Performance

Build all views as tables

Discussion in 'Contribute Your SQL Server Scripts' started by benwilson, Aug 22, 2005.

  1. benwilson New Member

    In our database, we have some complex reports that are built on some very complex views. We have found that some of these reports take a LONG time to run so decided to create a reporting database and to build all the user views in the database as tables with the same name as the view had so that all the existing code will work. After the full backup is completed each night, it is restored to the reporting database and the following script is run...Any comments or tips (or criticisms[V]) are welcome! it seems to speed up the reports greatly!

    --Declare variables
    DECLARE @ViewName_ NVARCHAR(200),
    @TempTableName_ NVARCHAR (200),
    @SQL_ NVARCHAR(4000)


    --Insert the name of all user defined view prefixed with 'view_' into the new table tbl_ViewList
    SELECT TABLE_NAME
    INTO tbl_ViewList
    FROM INFORMATION_SCHEMA.VIEWS
    WHERE TABLE_NAME LIKE 'view_%'

    --Declare cursor to step through the view names in tbl_ViewList
    DECLARE ViewCursor CURSOR FOR
    SELECT TABLE_NAME
    FROM tbl_ViewList

    OPEN ViewCursor

    FETCH NEXT FROM ViewCursor INTO @ViewName_

    WHILE @@FETCH_STATUS = 0
    BEGIN
    BEGIN TRAN

    --Create a temporary name for the table that will be created
    SET @TempTableName_ = 'temp_' + @ViewName_

    --Create and execute a SQL string to insert all data from the view into @TempTableName_
    SET @SQL_ = 'SELECT * INTO [' + @TempTableName_ + '] FROM [' + @ViewName_ + ']'
    EXECUTE (@SQL_)

    --If an error occurs, rollback the transaction and go to the next view
    IF @@Error <> 0
    BEGIN
    ROLLBACK TRAN
    GOTO fetcher
    END

    --Create and execute a SQL string to drop the view
    SET @SQL_ = 'DROP VIEW [' + @ViewName_ + ']'
    EXECUTE (@SQL_)

    --If an error occurs, rollback the transaction and go to the next view
    IF @@Error <> 0
    BEGIN
    ROLLBACK TRAN
    GOTO fetcher
    END

    --Create and execute a SQL string to rename the temp table to the name of the view it was built from
    SET @SQL_ = 'EXEC sp_rename [' + @TempTableName_ + '], [' + @ViewName_ + ']'
    EXECUTE (@SQL_)

    --If an error occurs, rollback the transaction and go to the next view
    IF @@Error <> 0
    BEGIN
    ROLLBACK TRAN
    GOTO fetcher
    END

    COMMIT TRAN

    fetcher:
    FETCH NEXT FROM ViewCursor INTO @ViewName_
    END

    CLOSE ViewCursor
    DEALLOCATE ViewCursor


    'I reject your reality and substitute my own' - Adam Savage
  2. dineshasanka Moderator

    Seems to be a good script but just copy and paste won;t work as all the coding are juggled.
  3. dineshasanka Moderator

    I manage to get the code
    after all it returns me
    (0 row(s) affected)

    and with empty view
    what may have the cause
  4. benwilson New Member

    Hi Dinesh,
    There seems to be some issue with copying and pasting from posts that use the
    Code:
     tags...i had the same problem yesterday with another post :-(
    
    As for why it didnt work, all i can think is that you need to change the following line to use whatever prefix you use for user views in your database:
    WHERE TABLE_NAME LIKE 'view_%' 
    
    apart from that, i dont know! it works in our system fine...
    
    'I reject your reality and substitute my own' - Adam Savage 

Share This Page