Table Comparison question

Hello Community,

I'm trying to compare the column heading between two tables and have the difference highlighted in a new table.

For example, in the image below, you can see that the only difference in the between the headings between Table A and Table B is header D. I would like to be able to generate a sql script that will catch the differences and place the difference in a column beside or in a row below as shown in the image.

Any help greatly appreciatd.



I don't have any usable data to test with, but I think should at least be very close:

DECLARE @table1 nvarchar(128)
DECLARE @table2 nvarchar(128)

SET @table1 = 'Table A'
SET @table2 = 'Table B'

SELECT COALESCE(, AS column_name,
    @table1 AS table1,
    @table2 AS table2,
    CASE WHEN MAX( IS NULL THEN 'Yes' ELSE 'No' END AS is_in_table1, 
    CASE WHEN MAX( IS NULL THEN 'Yes' ELSE 'No' END AS is_in_table2 
FROM sys.columns t1
FULL OUTER JOIN sys.columns t2 ON =
WHERE t1.object_id = OBJECT_ID(@table1) AND
    t2.object_id = OBJECT_ID(@table2) AND
ORDER BY column_name

Hi Scott,

Thanks for reaching out.

Unfortunately, the query didn't work as expected. See image


As you can see there is difference between PRODUCT_TEST and PRODUCT_ID

However, this isn't reflected in the results

Ok. Again, without usable test data, I've gone as far as I can go.

OK, thanks again. I'm not sure how much usable data you would need? I'm not even sure if you need data at all? All that I'm trying to do is determine differences between column headings.

My apologies if I haven't explained myself very well

Please provide the data requested by @ScottPletcher, otherwise it would be guess work. Then you will respond with "Unfortunately, the query didn't work as expected"

Posting data with pictures is one way but very difficult for us to help you as it would take time to recreate it. Here is how you can provide more usable data.

create table product(product_test int, country_cd varchar(10), 

insert into product
select 1, 'DE', 1, 'P', 'Bitte geben Sie Beispieldaten an'

"Usable data" in this case would be the DDL to create the two table structures you want compared.

I'm not sure how you expect someone to write perfect code without sample data to test it on. But I admit I can't always do that. I'm willing to volunteer my time to help others, but not if they don't feel they should even have to provide a basic data/structure setup.

Good luck with your q.

Hi guys,

I'm really sorry for not providing 'usable data'. I'm in transist at the moment and will provide usable data as soon as I'm stationary.

I understand that the help provided on these forums are voluntary and I'm very appreciative .


Just one other thing. Can you let me know how to provide usable data? This forum prevents uloading .csv ro .txt files

For something reasonably short, you can easily just copy/paste it in:

CREATE TABLE dbo.tableA ( [Header A] ... );
CREATE TABLE dbo.tableB ( ... );

Hi ScottPletcher,

Thanks for sticking with me on this. Here is a sample Table
CREATE TABLE Optimus.dbo.table1 (
header1 VARCHAR(50) NULL
,header2 VARCHAR(50) NULL
,header3 VARCHAR(50) NULL

CREATE TABLE Optimus.dbo.table2 (
header1 VARCHAR(50) NULL
,header2 VARCHAR(50) NULL
,header33 VARCHAR(50) NULL

Is the above ok?


OK, this is at least quite a bit closer:

DECLARE @table1 nvarchar(128)
DECLARE @table2 nvarchar(128)

SET @table1 = 'Table1'
SET @table2 = 'Table2'

    SELECT name AS t1_column_name
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@table1)
) AS t1
    SELECT name AS t2_column_name
    FROM sys.columns
    WHERE object_id = OBJECT_ID(@table2)
) AS t2 ON t1.t1_column_name = t2.t2_column_name

Hi Scott,

It's looking good, but I a little tricky to follow. Really appreciate your assistance with this