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

Question

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

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

 Select name from sysobjects where name like 'Warehouse_Inventory%'

我该如何使用这些表名并创建一个视图？

注意：我只选择了10个常见的列。如果某个表中不存在某个列，我希望将其显示为NULL。

- turbo88

---

除非这些表中的所有列都相同，否则将很难对这些表执行联合操作。您需要使用动态SQL来完成此操作。如果您正在寻找的是JOIN而不是UNION，则仍然必须硬编码要连接的列。 - Sam

假设我只选择所有表中存在的列。如果它们不存在，我会显示 NULL。 - turbo88

如何对所有表执行联合全部（union all）操作？它们是否具有相同的数据类型和相同数量的列？ - Shakeer Mirza

Answer 1

这个查询可能会对你有所帮助...

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

- Rajesh Ranjan

---

这是他可以查看的内容，但在运行所有返回的选择语句时会出现错误，因为所有表的列列表不相同。 - Sam

此查询在最后一行打印 UNION ALL。 - turbo88

如果它是一次性的活动，请手动删除那个UNION ALL，否则将使用XML或@Variable合并到单行中以删除最后的UNION ALL……在这种情况下哪个更简单呢....... :) - Rajesh Ranjan

Answer 2

我不确定为什么您想使用sys.sysojects而不是其他sys视图。另外，当您想要联接所有表时，不确定为什么您想要按表名进行搜索..... 如果您有200个表，由于查询的大小，我可能会建议在表和临时表上使用光标来保存结果，但是如果您真的想通过union all来完成它，以下是一种方法...

构建一个包含您想要的10列的列表，然后运行查询。您可能需要调整并添加一些转换/转换函数以确保所有内容都是正确的数据类型，可以通过sys.types和sys.columns动态完成，或者只需通过修改下面我的动态sql中的所有内容为NVARCHAR(???)并前进。

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

- Matt