Compare column names between two database tables in SQL Server

Hi PRA,

Refer below query.

;WITH tbl1 AS (
    SELECT t.name AS TableName, c.Name AS ColumnName
    FROM server1.dbname.sys.tables t
    INNER JOIN server1.dbname.sys.columns c ON t.object_id = c.object_id
    WHERE t.name = 'tablename'
),
tbl2 AS (
    SELECT t.name AS TableName, c.Name AS ColumnName
    FROM server2.dbname.sys.tables t
    INNER JOIN server2.dbname.sys.columns c ON t.object_id = c.object_id
    WHERE t.name = 'tablename'
)

-- find the columns that does not exist in tbl2
SELECT * FROM tbl1
LEFT JOIN tbl2 ON tbl1.ColumnName = tbl2.ColumnName
WHERE tbl2.ColumnName IS NULL

Reference:

https://dataedo.com/kb/query/sql-server/compare-tables-and-columns-in-two-databases

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1908a20d-d9eb-4689-8b98-b51c4df65b52/how-to-compare-columns-of-two-tables-in-two-different-servers?forum=transactsql