使用sysobjects表中的表名创建Server视图

Question

我有大约200张桌子。我想从所有这些表中创建一个view。我觉得硬编码所有表名并在视图定义中执行UNION ALL是效率低下的。

相反，我计划从sysobjects表中检索表名，如

 Select name from sysobjects where name like 'Warehouse_Inventory%'

如何使用这些表名并从中创建view？

注意:我只选择了10个常见的列。如果表中没有列，我想为它显示NULL。

Answer 1

这个查询可能对您有帮助。

    SELECT 'CREATE VIEW VIEW_NAME AS'
    UNION ALL
    SELECT 'SELECT * FROM ['+NAME+']
    UNION ALL' FROM SYS.TABLES where name like 'Warehouse_Inventory%'

Answer 2

我不知道为什么要使用sys.sysojects而不是其他sys视图。现在也可以确定为什么当您想要合并所有表时，您想要通过一个表名进行搜索……我可能会建议在表和临时表上使用光标来保存结果，如果您有200个表，只需按查询的大小执行，但是如果您真的很想通过联合执行，这里有一种方法…

生成所需10列的列表。然后运行查询。您可能需要调整和添加一些强制转换/转换功能，以确保所有的数据类型都是正确的--这可以用sys.types和sys.columns动态地完成，或者只需确保所有内容都是NVARCHAR(？)通过修改下面的动态sql并继续前进。

DECLARE @ListOfColumns AS TABLE (ColumnName VARCHAR(100))
INSERT INTO @ListOfColumns (ColumnName) VALUES ('col1'),('col2'),('col3')

DECLARE @SQLStatement NVARCHAR(MAX)

;WITH cteColumnsTableCross AS (
    SELECT
       SchemaName = s.name
       ,t.schema_id
       ,TableName = t.name
       ,l.ColumnName
    FROm
       @ListOfColumns l
       CROSS JOIN sys.tables t
       INNER JOIN sys.schemas s
       ON t.schema_id = s.schema_id
)


, cteColumns AS (
    SELECT
       x.SchemaName
       ,x.TableName
       ,x.ColumnName
       ,ColumnExists = IIF(c.name IS NOT NULL,1,0)
       ,RowNum = ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY x.TableName DESC)
       --you can add data type by getting from sys.columns and sys.types if desired
    FROM
       cteColumnsTableCross x
       LEFT JOIN sys.tables t
       ON x.TableName = t.name
       AND x.schema_id = t.schema_id
       LEFT JOIN sys.columns c
       ON t.object_id = c.object_id
       AND x.ColumnName = c.name
)

, cteSelectStatements AS (
    SELECT
       TableName = t.name
        ,TableSelect = 'SELECT TableName = ''' + t.name +  ''', ' + 

            STUFF(
            (SELECT ', ' + c.ColumnName + ' = ' + IIF(c.ColumnExists = 0,'NULL',c.ColumnName)
            FROM
                cteColumns c
            WHERE t.name = c.Tablename
            FOR XML PATH(''))
            ,1,1,'')

          + ' FROM ' + t.name + 

          IIF((ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY t.name DESC)) > 1,' UNION ALL ','')
    FROM
       sys.tables t
)

SELECT @SQLStatement = STUFF(
        (SELECT ' ' + TableSelect
        FROM
            cteSelectStatements
       ORDER BY
          TableName
        FOR XML PATH(''))

        ,1,1,'')

PRINT @SQLStatement
--EXECUTE @SQLStatement