T-SQL comparing columns in two tables

Just another little T-SQL nuget here which I hope might help others, I recently had to do a quick check between two tables where they were both copies of the same original table but had been modified slightly. I wasn’t interested in the data, just which columns had been added or changed between the two tables. Luckily there is a handy way to do this using the T-SQL set operator EXCEPT. Quite often you’ll see this operator used to compare result sets of two tables to find where rows exist in one but not the other, a bit like this:-

SELECT * FROM table1

EXCEPT

SELECT * FROM table2

However this is quite simple to modify to allow us to check which columns exist in one table but not the other, we can use the INFORMATION_SCHEMA.COLUMNS view to get the columns from both tables and in combination with the EXCEPT operator we can see where there are differences:-

-- Set the names of the tables we want to check
DECLARE @tableSchema1 VARCHAR(500) = 'dbo'
DECLARE @tableName1 VARCHAR(500) = 'SomeTableName'
DECLARE @tableSchema2 VARCHAR(500) = 'dbo'
DECLARE @tableName2 VARCHAR(500) = 'OtherTableName'

-- Find columns in table 1 that are NOT in table 2
SELECT
[ColumnsInTable1ThatAreNotInTable2] = [COLUMN_NAME]

FROM
[INFORMATION_SCHEMA].[COLUMNS]

WHERE
[TABLE_SCHEMA] = @tableSchema1
AND [TABLE_NAME] = @tableName1

EXCEPT

SELECT
[COLUMN_NAME]

FROM
[INFORMATION_SCHEMA].[COLUMNS]

WHERE
[TABLE_SCHEMA] = @tableSchema2
AND [TABLE_NAME] = @tableName2

ORDER BY
[COLUMN_NAME]

-- Find columns in table 2 that are NOT in table 1
SELECT
[ColumnsInTable2ThatAreNotInTable1] = [COLUMN_NAME]

FROM
[INFORMATION_SCHEMA].[COLUMNS]

WHERE
[TABLE_SCHEMA] = @tableSchema2
AND [TABLE_NAME] = @tableName2

EXCEPT

SELECT
[COLUMN_NAME]

FROM
[INFORMATION_SCHEMA].[COLUMNS]

WHERE
[TABLE_SCHEMA] = @tableSchema1
AND [TABLE_NAME] = @tableName1

ORDER BY
[COLUMN_NAME]