How to compare the columns of two tables, SQL Server?

Question

I understand creating a join and comparing the values of specific columns from two tables. In this case, I am only interested in comparing the columns between two different tables, not the values.

I want to check to see if I have the correct number of columns in my new table, and that the spelling of each column in my new table matches the other. Essentially, a way to QC the schema of the new table.

Any suggestions for a SQL query to execute this?

You could compare the results of this query for each table `SELECT * FROM sys.columns WHERE Object_ID = Object_ID(N'tableName')` [I found the query on this answer](http://stackoverflow.com/a/133057/1804496). — Zack, Sep 27 '16 at 16:15

score 2 · Accepted Answer · answered Sep 27 '16 at 16:16

2

You can utilize the table below:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_CATALOG = SOME DB
AND TABLE_NAME = SOME TBL

Or you can use sys.columns similarly.

answered Sep 27 '16 at 16:16

Wyatt Shipman

1,669
1
10
22

score 0 · Answer 2 · answered Sep 27 '16 at 16:19

You want to check whether the Data Definition is correct. You cannot do so using a Data Query on those tables.

However, SQL Server stores the information about the tables in the database in other tables. See: https://msdn.microsoft.com/en-us/library/ms186778.aspx

In this case you might get what you want using a query on the INFORMATION_SCHEMA.COLUMNS table.

score 0 · Answer 3 · answered Sep 27 '16 at 17:00

;WITH cteTable1Columns AS (
    SELECT 
       c.object_id
       ,c.name
    FROM
       sys.columns c
    WHERE
       c.object_id = object_id('A')
)

, cteTable2Columns AS (
    SELECT 
       c.object_id
       ,c.name
    FROM
       sys.columns c
    WHERE
       c.object_id = object_id('B')
)

SELECT
    OBJECT_NAME(c1.object_id) as Table1Name
    ,c1.name as Table1ColName
    ,OBJECT_NAME(c2.object_id) as Table2name
    ,c2.name as Table2ColName
FROM
    cteTable1Columns c1
    FULL JOIN cteTable2Columns c2
    ON c1.name = c2.name
WHERE
    c1.object_id is NULL
    OR c2.object_id IS NULL

If you use a method like this to identify the columns in each table then do a full outer join on name it will show you all of your problems if any exist. (note you can also use INFORMATION_SCHEMA.COLUMNS)

How to compare the columns of two tables, SQL Server?

3 Answers3