SQL Server 2008 R2: Alter view

Question

I want to write a stored procedure to alter the view by passing the view name as a parameter to the stored procedure.

Example:

I have a view called as view1 which selects from 4 tables called tb1, tb2, tb3, tb4 which have updated with some modifications.

Now I want the view to be latest updated with the updated tables.

Attempt:

create proc alterview
   @viewname varchar(50)
AS
   Declare @query nvarchar(max)
   Declare @TableNames varchar(max)

   SET @query = 'DECLARE Cursor_Get_Tables CURSOR fast_forward for 
                    SELECT Table_Name
                    FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
                    WHERE View_Name = ''' + @ViewName + ''''                     

   EXEC sp_executesql @query


   OPEN Cursor_Get_Tables

   FETCH NEXT FROM Cursor_Get_Tables INTO @TableNames

   WHILE(@@fetch_status = 0)
   BEGIN   
       /* STUCK here */
       SET @query = 'ALTER VIEW ' + @ViewName + ' AS SELECT * FROM ' + @TableNames+ ' 
       /* Not getting to get all tables into single alter view script here */

       EXEC(@SQL)

       FETCH NEXT FROM Cursor_Get_Tables INTO @TableNames
   END

   CLOSE Cursor_Get_Tables      
   DEALLOCATE Cursor_Get_Tables
GO  

Note: I am not getting how to get all tables into single ALTER VIEW script like:

ALTER VIEW View1 AS 
   SELECT * FROM tb1 
   UNION 
   SELECT * FROM tb2 
   UNION 
   SELECT * FROM tb3 
   UNION 
   SELECT * FROM tb4 
   UNION;

Answer 1

You need to keep 'ALTER VIEW ViewName AS' outside the while loop. Then append the select statement with alter view statement inside the loop. Finally outside of the loop execute the query.

OPEN Cursor_Get_Tables

FETCH NEXT FROM Cursor_Get_Tables INTO @TableNames

SET @query = 'ALTER VIEW ' + @ViewName + ' AS '

WHILE( @@FETCH_STATUS = 0 )
  BEGIN
      SET @query += 'SELECT * FROM ' + @TableNames + ' Union '

      FETCH NEXT FROM Cursor_Get_Tables INTO @TableNames
  END

CLOSE Cursor_Get_Tables

DEALLOCATE Cursor_Get_Tables

SELECT @query = LEFT(@query, Len(@query) - 5)

EXEC(@SQL) 

Answer 2

A cursor is not needed here. Try:

CREATE PROC alterview
    @ViewName sysname
AS

DECLARE @SQL nvarchar(MAX);

SELECT @SQL = 
    'ALTER VIEW ' + @ViewName
     + ' AS SELECT * FROM '
     + STUFF((SELECT ' UNION ALL SELECT * FROM ' + TABLE_NAME
        FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE
        WHERE VIEW_NAME = @viewname
        FOR XML PATH('')), 1, 11, '') + ';';
EXECUTE (@SQL);
GO