CODEWITHSUNDEEP
sql-servert-sqldynamic-sql
Azhar
Azhar

Reputation: 25

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

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.

Upvotes: 0

Views: 1036

Answers (2)

JohnLBevan
JohnLBevan

Reputation: 24410

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.

Upvotes: 1

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67291

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.

Upvotes: 1

Related Questions