--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. |
