Stack multiple tables using UNION ALL

,

I often encounter situations where I need to gather data from several tables together and analyze it, looking for anomalies and inconsistencies. This often requires me to build several UNION or UNION ALL statements use '' (blank) as [MISSING_COLUMN] as placeholders. Whether I drop the Columns folder from SSMS or use INFORMATION_SCHEMA.COLUMNS targeting specific tables, this can take a bit of time.

This handy little script does all the stitching for you, putting the crucial placeholders exactly where they need to go.

Remember the rules for data_types when using UNION statements. You may be required to convert non-string values to VARCHAR/NVARCHAR/CHAR where appropriate.

The first part of the script creates some tables used as examples. The remainder of the script could easily be modified as a stored procedure.

The main part of this script is creating all possible combinations of table and column. Once that is created, the second part of the script find which combinations actually exist, then superimposes the required

'' AS [MISSING_COLUMN] placeholder. Finally, STUFF for XML PATH('') turns that into an executable script. I added the UNION_ALL column to make sure those were placed into the @SC property correctly.

Theoretically, this could work for any collection of tables so long as the total number of columns is manageable (insert Excel plugin here)

 

Thanks for reading.

 

Antony Liberato

Sr. Database Administrator

--SETUP
CREATE TABLE DBO.TABLE1 (NAME1 VARCHAR(100), NAME2 VARCHAR(100), NAME3 VArchar(100))
	INSERT INTO TABLE1 ([NAME1], [NAME2], [NAME3])
		VALUES('T1N1','T1N2','T1N3') 
CREATE TABLE DBO.TABLE2 (NAME1 VARCHAR(100), NAME2 VARCHAR(100), NAME3 VArchar(100),  NAME5 VARCHAR(100), NAME6 VArchar(100))
	INSERT INTO TABLE2 ([NAME1], [NAME2], [NAME3], [NAME5], [NAME6])
		VALUES('T2N1','T2N2','T2N3','T2N5','T2N6') 
CREATE TABLE DBO.TABLE3 (NAME1 VARCHAR(100), NAME3 VARCHAR(100), NAME5 VArchar(100), NAME7 varchar(100))
	INSERT INTO TABLE3 ([NAME1], [NAME3], [NAME5], [NAME7])
		VALUES('T3N1','T3N3','T3N5','T3N7') 
CREATE TABLE DBO.TABLE4 (NAME4 VARCHAR(100))
	INSERT INTO TABLE4 ([NAME4])
		VALUES('T4N4') 
--END SETUP

BEGIN TRY
DROP TABLE #MAPPER
END TRY
BEGIN CATCH
PRINT '#MAPPER ALREADY DROPPED'
END CATCH
 
--STORE T TABLE LIST INTO A TEMP TABLE FOR CONSISTENCY
BEGIN TRY
DROP TABLE #TABLES
END TRY
BEGIN CATCH
PRINT '#TABLES ALREADY DROPPED'
END CATCH
SELECT 
IIF(TID>1, 'UNION ALL','') AS UNION_ALL
,TABLE_NAME
INTO #TABLES
FROM
(
	SELECT 
	TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME, ROW_NUMBER() OVER (ORDER BY TABLE_NAME) AS TID
	FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_NAME LIKE 'TABLE_'
	GROUP BY TABLE_SCHEMA , TABLE_NAME
) AS A
--STORE THE COLUMNS INTO ANOTHER TEMP TABLE
;WITH _COLUMNS
AS
(
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS TABLE_NAME, COLUMN_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME LIKE 'TABLE_'
)
, _TABLE_COL
AS
(
	SELECT TABLE_NAME, COLUMN_NAME
	FROM 
	(
		SELECT 
		COLUMN_NAME
		--INTO #ALL_COLUMNS
		FROM
		_COLUMNS
		GROUP BY COLUMN_NAME
	) AS A,
	 --NOW ENUMERATE ALL TO CREATE TABLE/COLUMN COMBINATIONS FOR ALL POSSIBLE
	 (
	 SELECT 
		TABLE_NAME
		FROM 
		#TABLES
	 ) AS B
),
 _MAPPER
AS
(
	SELECT 
	TC.TABLE_NAME, TC.COLUMN_NAME, IIF(B.COLUMN_NAME IS NULL, ''''' AS [' + TC.COLUMN_NAME + ']', B.COLUMN_NAME) AS MAPPER
	,ROW_NUMBER() OVER (PARTITION BY TC.TABLE_NAME ORDER BY TC.COLUMN_NAME) AS COLIDE
	FROM 
	_TABLE_COL TC
	LEFT JOIN
	_COLUMNS AS B
	ON TC.TABLE_NAME = B.TABLE_NAME AND TC.COLUMN_NAME = B.COLUMN_NAME
)
 SELECT * INTO #MAPPER FROM _MAPPER

 DECLARE @SC NVARCHAR(MAX) = '--BEGIN
 '
 SELECT @SC = @SC + '
 ' + UNION_ALL + ' ' + '
 SELECT ''' + T.TABLE_NAME + ''' AS META_TABLE_NAME, ' + STUFF((SELECT ', ' + M.MAPPER FROM #MAPPER M WHERE M.TABLE_NAME = T.TABLE_NAME FOR XML PATH('')),1,1,'') + ' FROM ' + TABLE_NAME
  FROM #TABLES T
 
 PRINT @SC
 EXEC SP_EXECUTESQL @SC
 
  
 SELECT  NAME1, NAME2, NAME3, '' AS [NAME4], '' AS [NAME5], '' AS [NAME6], '' AS [NAME7] FROM dbo.TABLE1
UNION ALL
 SELECT  NAME1, NAME2, NAME3, '' AS [NAME4], NAME5, NAME6, '' AS [NAME7] FROM dbo.TABLE2
UNION ALL
 SELECT  NAME1, '' AS [NAME2], NAME3, '' AS [NAME4], NAME5, '' AS [NAME6], NAME7 FROM dbo.TABLE3
UNION ALL
 SELECT  '' AS [NAME1], '' AS [NAME2], '' AS [NAME3], NAME4, '' AS [NAME5], '' AS [NAME6], '' AS [NAME7] FROM dbo.TABLE4


 


 --GET COLUMNS
 --CREATE ALL POSSIBLE COMBINATIONS
 --COMARE WITH EXISTING COMBINATIONS
 --REPLACE NON-EXISTENT COMBINATIONS WITH BLANK REPLACEMENT PLACEHOLDERS.

Rate

Share

Share

Rate