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.
Cheers
Carlton
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(t1.name, t2.name) AS column_name,
@table1 AS table1,
@table2 AS table2,
CASE WHEN MAX(t1.name) IS NULL THEN 'Yes' ELSE 'No' END AS is_in_table1,
CASE WHEN MAX(t2.name) IS NULL THEN 'Yes' ELSE 'No' END AS is_in_table2
FROM sys.columns t1
FULL OUTER JOIN sys.columns t2 ON t2.name = t1.name
WHERE t1.object_id = OBJECT_ID(@table1) AND
t2.object_id = OBJECT_ID(@table2) AND
(t1.name IS NULL OR t2.name IS NULL)
GROUP BY COALESCE(t1.name, t2.name)
ORDER BY column_nameHi 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),
AK_PRODUCT_ID int, AK_PRODUCT_GIFT_ID CHAR(1), PRODUCT_DESC varchar(50))
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 .
Cheers
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
)
GO
CREATE TABLE Optimus.dbo.table2 (
header1 VARCHAR(50) NULL
,header2 VARCHAR(50) NULL
,header33 VARCHAR(50) NULL
)
GO
Is the above ok?
Perfect!
OK, this is at least quite a bit closer:
DECLARE @table1 nvarchar(128)
DECLARE @table2 nvarchar(128)
SET @table1 = 'Table1'
SET @table2 = 'Table2'
SELECT *
FROM (
SELECT name AS t1_column_name
FROM sys.columns
WHERE object_id = OBJECT_ID(@table1)
) AS t1
FULL OUTER JOIN (
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_nameHi Scott,
It's looking good, but I a little tricky to follow. Really appreciate your assistance with this