如何为数据库中的所有表创建视图? [英] How to create view for all tables in database?

问题描述

我想从数据库中已经存在的所有表中创建视图，这是一项艰巨的任务，要一张一张地捕获表并使创建视图 XXXX 为 select * from Table_name .我发现使用游标可以实现的东西，代码是:

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

推荐答案

这就是我对动态 SQL 所做的；首先检查视图是否存在，然后使用所有列创建它(不是使用 SELECT * FROM...).

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

这篇关于如何为数据库中的所有表创建视图?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！