Display All Columns from all Table by using Union ALL with Different no of Columns in Each Table

Question

I have Three Tables with Different no of Columns. e.g T1(C1), T2(C1,C2,C3), T3(C1,C4). I want to generate a Dynamic SQL that will create a View like

    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

I have achieved this goal by using two nested loop by Checking Each column If It is Existed in a table or not.
But in Production we have around 30 tables with around 60 Columns in Each table. Create of Dynamic SQL is taking around 7 minutes and this is not Acceptable to us. We want to improve performance Further.

Immediate help would be highly appreciated.

Please take a minute to read [Ask], and [edit] your question accordingly. We need to see your existing code, not just the final result. — Zohar Peled, Nov 05 '18 at 10:46
What is the actual, underlying problem that you are trying to solve with something like that? I can't imagine any reason why a view containing the data of all tables might be helpful — , Nov 05 '18 at 11:13
you are almost correct. we want to have all data from all tables into a one View But they have slight different no of columns. so when i make union of all tables , i see Error. to resolve the issue, I created Dynamic SQL to check if column exists in a table or not. If there is not column in table I add this as NULL AS Col. — Azhar, Nov 05 '18 at 11:22
Can we assume that if two tables have columns with a shared name, they also have a shared definition? Or could `table1` have field `Id` set to `bigint` whilst `table2` has `Id` set to `uniqueidentifier` (for example)? — JohnLBevan, Nov 05 '18 at 11:27
Also, how often do your list of tables / their column definitions change? i.e. Do you want to run the dynamic SQL in production; or could you use dynamic SQL to generate a `create table` statement which you then use going forwards / only rerun this dynamic sql when there are schema changes to the related tables? That will improve performance for very little overhead. — JohnLBevan, Nov 05 '18 at 11:28

JohnLBevan · Accepted Answer · 2018-11-05T12:01:39.483

Here's some Dynamic SQL which would create and execute what you describe. How does this compare to your current SQL's performance?

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)

As mentioned in the comments, rather than running this dynamic SQL every time you need to execute this query, you could use it to generate a view which you can then reuse as required.

Adding indexes and filters to the underlying tables as appropriate could further improve performance; but without knowing more of the context, we can't give much advise on specifics.

Good point @Shnugo; for older versions, various tactics are available per https://stackoverflow.com/a/13681687/361842 — JohnLBevan, Nov 05 '18 at 11:50

Shnugo · Answer 2 · 2018-11-05T11:44:29.313

You might try this:

I use some general tables where I know, that they share some of their columns to show the principles. Just replace the tables with your own tables:

Attention: I do not use these INFORMATION_SCHEMA tables to read their content. They serve as examples with overlapping columns...

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);

Short explanation:

The first row of each table will be tranformed into an XML. Using AUTO-mode will use the table's name in the <root> and add all columns as nested elements.

The second CTE will create a distinct list of all columns existing in any of the tables.

the third CTE will extract all Tables with their connected columns.

The final SELECT will use a nested string-concatenation to create a UNION ALL SELECT of all columns. The existance of a given name will decide, whether the column is called with its name or as NULL.

Just use PRINT to print out the @statement in order to see the resulting dynamically created SQL command.

Display All Columns from all Table by using Union ALL with Different no of Columns in Each Table

2 Answers2