SQL Matching table to what Field Name columns Match and don't Match

1

Hi,

I am not sure if this can be don, but I have a few tables that I would like to compare. I want to see what Field Name match other tables.

Example if I have Table A that has First Name, Last Name, Address, ZIp, etc and Table B has First, Name, Last Name, Middle Name, Address, Zip etc.

I want to know that Table A is different because it doesn't have the field Middle Name.

I know we can match on records, but I actually want to match on field Names.

Can this be done?

Thanks in advance.

2 
;WITH a AS ( SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TableA'),
b AS (SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'TableB')
SELECT
	a.name AS ColFromTableA,
	b.name AS ColFromTableB
FROM
	a FULL JOIN b ON a.name = b.name
3 
DECLARE @table1_name nvarchar(128)
DECLARE @table2_name nvarchar(128)

SET @table1_name = 'dbo.tableA'
SET @table2_name = 'dbo.tableB'

SELECT COALESCE(c_t1.name, c_t2.name) AS column_name,
    CASE WHEN c_t1.name > '' AND c_t2.name > '' THEN '' ELSE '**' END AS [mismatch_flag],
    CASE WHEN c_t1.name IS NULL THEN 'Yes' ELSE 'No' END AS [is_in_table1?],
    CASE WHEN c_t2.name IS NULL THEN 'Yes' ELSE 'No' END AS [is_in_table2?]
FROM sys.columns c_t1
FULL OUTER JOIN sys.columns c_t2 ON c_t2.object_id = OBJECT_ID(@table2_name) AND 
    c_t2.name = c_t1.name
WHERE c_t1.object_id = OBJECT_ID(@table1_name)
ORDER BY column_name
4

Thanks this worked for me!

5

Thanks this worked for me!