使用Union All显示所有表中的所有列，每个表中的列数不同

Question

我有三个不同列数的表。例如T1(C1(、T2(C1、C2、C3(、T3(C1、C4(。我想生成一个动态SQL，它将创建一个类似的视图

CREATE VIEW [dbo].[vwData]
AS 
SELECT C1,NULL AS C2,NULL AS C3,NULL AS C4
FROM DBO.T1
UNION ALL 
SELECT C1,C2,C3,NULL AS C4
FROM DBO.T2
UNION ALL 
SELECT C1,NULL AS C2,NULL AS C3,C4
FROM DBO.T3

我通过检查每列是否存在于表中来使用两个嵌套循环来实现这个目标
但在生产中，我们有大约30张表，每张表中大约有60列。创建动态SQL大约需要7分钟，这是我们无法接受的。我们希望进一步提高性能。

如能立即提供帮助，我们将不胜感激。

Answer 1

下面是一些动态SQL，它将创建并执行您所描述的内容。这与您当前SQL的性能相比如何？

Fiddle：https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=800747a3d832e6e29a15484665f5cc8b

declare @tablesOfInterest table(tableName sysname, sql nvarchar(max))
declare @allColumns table(columnName sysname)
declare @sql nvarchar(max)
insert @tablesOfInterest(tableName) values ('table1'), ('table2')
insert @allColumns (columnName)
select distinct c.name
from sys.columns c 
where c.object_id in
(
select object_id(tableName)
from @tablesOfInterest
)
update t
set sql = 'select ' + columnSql + ' from ' + quotename(tableName)
from @tablesOfInterest t
cross apply
(
select string_agg(coalesce(quotename(c.Name), 'null') + ' ' + quotename(ac.columnName), ', ') within group (order by ac.columnName)
from @allColumns ac
left outer join sys.columns c
on c.object_id = object_id(t.tableName)
and c.Name = ac.columnName
) x(columnSql)

select @sql = string_agg(sql, ' union all ')
from @tablesOfInterest
print @sql
exec (@sql)

正如注释中所提到的，您可以使用它生成一个视图，然后根据需要重用该视图，而不是每次需要执行该查询时都运行该动态SQL。

在基础表中适当添加索引和筛选器可以进一步提高性能；但如果不了解更多的背景，我们就无法就具体情况给出太多建议。

Answer 2

你可以试试这个：

我使用一些我知道的通用表，它们共享一些列来显示原理。只需将表格替换为您自己的表格：

注意：我不使用这些INFORMATION_SCHEMA表来读取其内容。它们是具有重叠列的示例。。。

DECLARE @statement NVARCHAR(MAX);
WITH cte(x) AS
(
SELECT
(SELECT TOP 1 * FROM INFORMATION_SCHEMA.TABLES FOR XML AUTO, ELEMENTS XSINIL,TYPE) AS [*]
,(SELECT TOP 1 * FROM INFORMATION_SCHEMA.COLUMNS FOR XML AUTO, ELEMENTS XSINIL,TYPE) AS [*]
,(SELECT TOP 1 * FROM INFORMATION_SCHEMA.ROUTINES FOR XML AUTO, ELEMENTS XSINIL,TYPE) AS [*]
--add all your tables here...
FOR XML PATH(''),TYPE
)
,AllColumns AS
(
SELECT DISTINCT a.value('local-name(.)','nvarchar(max)') AS ColumnName
FROM cte
CROSS APPLY x.nodes('/*/*') A(a)
)
,AllTables As
(
SELECT a.value('local-name(.)','nvarchar(max)') AS TableName
,a.query('*') ConnectedColumns
FROM cte
CROSS APPLY x.nodes('/*') A(a)
)
SELECT @statement=
STUFF((
(
SELECT 'UNION ALL SELECT ' +
'''' + TableName + ''' AS SourceTableName ' +
(
SELECT ',' + CASE WHEN ConnectedColumns.exist('/*[local-name()=sql:column("ColumnName")]')=1 THEN QUOTENAME(ColumnName) ELSE 'NULL' END + ' AS ' + QUOTENAME(ColumnName)   
FROM AllColumns ac
FOR XML PATH('root'),TYPE
).value('.','nvarchar(max)') + 
' FROM ' + REPLACE(QUOTENAME(TableName),'.','].[')
FROM AllTables
FOR XML PATH(''),TYPE).value('.','nvarchar(max)')
),1,10,'');
EXEC( @statement); 

简要说明：

每个表的第一行将被转换为XML。使用AUTO模式将在<root>中使用表的名称，并将所有列添加为嵌套元素。

第二个CTE将创建任何表中存在的所有列的不同列表。

第三CTE将提取具有其连接列的所有表。

最后的SELECT将使用嵌套字符串串联来创建所有列的UNION ALL SELECT。给定名称的存在将决定该列是使用其名称调用还是作为NULL调用。

只需使用PRINT打印出@statement，即可查看生成的动态创建的SQL命令。