Build all views as tables

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

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