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