Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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