How to create view for all tables in database?

问题:

I want to make views from all tables already exist in database that’s hard task to catch the tables one by one and make create view XXXX as select * from Table_name .I find something that it is possible with cursor and the code is :

DECLARE @TableName sysname
DECLARE @ColumnCount INT
DECLARE @ColumnID INT
DECLARE @SelectColumn NVARCHAR(500)
DECLARE @sql NVARCHAR(max) = ''

DECLARE QUERYINFO CURSOR FOR
    SELECT
        t.name AS TableName,
        ccount.ColumnCount,
        c.column_id AS ColumnID,
        CASE WHEN c.column_id <> ccount.ColumnCount
                THEN c.name + ', '
             ELSE c.name
             END AS SelectColumn
    FROM sys.tables t
    INNER JOIN sys.columns c ON t.object_id=c.object_id
    INNER JOIN (
        SELECT object_id,COUNT(*) AS ColumnCount
        FROM sys.columns
        GROUP BY object_id
    ) ccount ON t.object_id = ccount.object_id
    ORDER BY t.Name,c.column_id

OPEN QUERYINFO
FETCH NEXT FROM QUERYINFO INTO @TableName,@ColumnCount,@ColumnID,@SelectColumn
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @ColumnID = 1
    BEGIN
        SET @sql = 'CREATE VIEW v_' + @TableName + ' AS SELECT ' + @SelectColumn
    END
    ELSE
    BEGIN
        SET @sql = @sql + @SelectColumn
    END 

    IF @ColumnID = @ColumnCount
    BEGIN
        SET @sql = @sql + ' FROM ' + @TableName
        EXEC sys.sp_executesql @sql
        SET @sql = ''
    END

    FETCH NEXT FROM QUERYINFO INTO @TableName,@ColumnCount,@ColumnID,@SelectColumn
END

CLOSE QUERYINFO
DEALLOCATE QUERYINFO

but it has error and I don't know how solve it

回答1:

This is what I would do with dynamic SQL; checking first to see if the view exists, then creating it with all of the columns (not by using SELECT * FROM...).

DECLARE @SQL nvarchar(MAX)
SET @SQL = N''


SELECT
    @SQL = @SQL + 
    N'IF EXISTS(SELECT 1 FROM sys.objects WHERE name = N''v_' + t.name + N''' AND type = N''V'') BEGIN DROP VIEW [v_' + t.name + '] END CREATE VIEW [v_' + t.name + N'] AS SELECT ' + 
        STUFF(
            (SELECT N',' + c.name
               FROM
                  sys.columns AS c
               WHERE 
                 c.OBJECT_ID = t.OBJECT_ID
               ORDER BY
                 column_id
               FOR XML PATH(''), TYPE).value('.',N'nvarchar(max)')
        ,1,1,N'')
        + N' FROM [' + t.name + N'];'
FROM
    sys.tables AS t

EXEC sp_executesql @SQL