Skip to content

Instantly share code, notes, and snippets.

@shaneis
Created April 20, 2017 20:49
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save shaneis/ab83eb70a3dc6d1ec4275e533fb0b6db to your computer and use it in GitHub Desktop.
Comparing column names between two tables.
SELECT Pivot1.ColumnName,
Pivot1.[dbo.DifferenceTable01],
Pivot1.[dbo.DifferenceTable02],
CASE WHEN [dbo.DifferenceTable01] = 1 AND [dbo.DifferenceTable02] = 1
THEN 'Both'
WHEN [dbo.DifferenceTable01] = 1 AND [dbo.DifferenceTable02] IS NULL
THEN 'Table 1 only'
WHEN [dbo.DifferenceTable01] IS NULL AND [dbo.DifferenceTable02] = 1
THEN 'Table 2 only'
ELSE 'Eh...this should not really happen'
END AS HumanReadableFormat
FROM ( SELECT
c.[name] AS ColumnName,
tb.TableName,
1 AS ColumnExists
FROM sys.columns AS c
RIGHT JOIN ( VALUES
(OBJECT_ID(N'dbo.DifferenceTable01', N'U'), 'dbo.DifferenceTable01'),
(OBJECT_ID(N'dbo.DifferenceTable02', N'U'), 'dbo.DifferenceTable02')
) AS tb (ObjectID, TableName)
ON c.object_id = tb.ObjectID
) AS UnPivotedColumns
PIVOT (
MAX(ColumnExists) FOR TableName IN ([dbo.DifferenceTable01], [dbo.DifferenceTable02])
) AS Pivot1
ORDER BY Pivot1.ColumnName ASC;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment