Skip to content

Instantly share code, notes, and snippets.

@shaneis
Created June 22, 2017 13:29
Show Gist options
  • Save shaneis/3eb5f00893afd631ced4b6df503fce00 to your computer and use it in GitHub Desktop.
Save shaneis/3eb5f00893afd631ced4b6df503fce00 to your computer and use it in GitHub Desktop.
Union Method Table Column Differences
SET STATISTICS IO, TIME ON;
DECLARE @table1 nvarchar(50) = 'dbo.TableColumnDifference01',
@table2 nvarchar(50) = 'dbo.TableColumnDifference02';
SET @table1 = PARSENAME(@table1, 1);
SET @table2 = PARSENAME(@table2, 1);
SELECT *
FROM
( SELECT COLUMN_NAME,
'Yes' AS Table1,
'No' AS table2
FROM
( SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1
EXCEPT
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
) AS tbl1
UNION ALL
SELECT COLUMN_NAME,
'No' AS Table1,
'Yes' AS table2
FROM
( SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
EXCEPT
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1
) AS tbl2
UNION ALL
SELECT COLUMN_NAME,
'Yes' AS Table1,
'Yes' AS table2
FROM
( SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table1
INTERSECT
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @table2
) AS tbl12
) AS tblall
ORDER BY COLUMN_NAME;
SET STATISTICS IO, TIME OFF;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment