Need to find out missing columns .

Article
04/21/2015

Question

_{Tuesday, April 21, 2015 8:35 PM}

We are trying to find out the difference between tables in CUSTOMER database and CUSTOMER_coded database.

The goal is to find out if there are any columns missing in each table of CUSTOMER_coded database.

Can you please help me? We need the list of tables in CUSTOMER_coded database that misses some column compare to its peer in CUSTOMER database (list of columns being missing also).

I googled, but I get only all the columns in tables of database.

I need missing columns of all the tables when we compare these 2 databases( CUSTOMER and CUSTOMER_coded databases).

Thanks in advance.

All replies (8)

_{Tuesday, April 21, 2015 9:05 PM ✅Answered}

Give this a try:

SELECT colName, tabName, COUNT(*), MAX(dbName), MIN(dbName)
  FROM (
        SELECT c.name AS colName, t.name AS tabName, 'CUSTOMER' AS dbName
          FROM CUSTOMER.sys.columns c
            INNER JOIN CUSTOMER.sys.tables t
              ON c.object_ID = t.object_ID
        UNION ALL
        SELECT c.name AS colName, t.name AS tabName, 'CUSTOMER_coded' AS dbName
          FROM CUSTOMER_coded.sys.columns c
            INNER JOIN CUSTOMER_coded.sys.tables t
              ON c.object_ID = t.object_ID
       ) a
 GROUP BY colName, tabName
 HAVING COUNT(*) <> 2
     OR MAX(dbName) <> 'CUSTOMER_coded'
     OR MIN(dbName) <> 'CUSTOMER'

Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question.

_{Tuesday, April 21, 2015 8:46 PM}

Are you trying to find out if the table schemas are different, or if the data in some of the columns in the two tables are different?

If it is the former,

SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER'
EXCEPT
SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER_coded';

SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER_coded';
EXCEPT
SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER'

If you are looking for missing data, do a similar query, except you would be querying the tables. For example:

SELECT col1, col2 FROM 'CUSTOMER'
EXCEPT
SELECT col1, col2 FROM 'CUSTOMER_coded';

SELECT col1, col2 FROM 'CUSTOMER_coded'
EXCEPT
SELECT col1, col2 FROM 'CUSTOMER'

_{Tuesday, April 21, 2015 8:53 PM}

Please try this:

SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME
FROM CUSTOMER.INFORMATION_SCHEMA.COLUMNS AS a
LEFT JOIN CUSTOMER_coded.INFORMATION_SCHEMA.COLUMNS AS b
ON b.TABLE_SCHEMA = a.TABLE_SCHEMA 
AND b.TABLE_NAME = a.TABLE_NAME
AND b.COLUMN_NAME = a.COLUMN_NAME
WHERE b.COLUMN_NAME IS NULL;

Saeid Hasani (My Writings on TechNet Wiki ,T-SQL Blog)

_{Tuesday, April 21, 2015 8:55 PM}

expanding on James idea...

create database TESTDB
CREATE database TEST
Go
use TESTDB
go
Create table ta(sno int)
Create table ta2(sno int,sname varchar(20))
go
use TEST
go
Create table ta1(sno int)
Create table ta2(sno int)

select a.name as [DatabaseName],b.name as [ColumnName] from TESTDB.sys.tables a inner join TESTDB.sys.columns b on a.object_id=b.object_id
except
select a.name as [DatabaseName],b.name  as [ColumnName] from TEST.sys.tables a inner join TEST.sys.columns b on a.object_id=b.object_id

select a.name as [DatabaseName],b.name  as [ColumnName]from TEST.sys.tables a inner join TEST.sys.columns b on a.object_id=b.object_id
except
select a.name as [DatabaseName],b.name  as [ColumnName] from TESTDB.sys.tables a inner join TESTDB.sys.columns b on a.object_id=b.object_id
--clean up
use master
go
drop database TESTDB
drop database TEST

Hope it Helps!!

_{Tuesday, April 21, 2015 8:55 PM}

Hi James,

Thanks for your reply, when I execute the query

SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER'
EXCEPT
SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER_coded';

SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER_coded';
EXCEPT
SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER'

it gives nothing,it just shows the column ' name' null .

Thanks,

_{Tuesday, April 21, 2015 9:05 PM}

That's good. So that means there are no missing columns. Just for sanity check, change one of the table names to some other table that you have in your database. You should see some columns in the output.

Another sanity check you can do is to change the EXCEPT keyword to INTERSECT. That will list all the columns that are common to both tables.

_{Tuesday, April 21, 2015 9:36 PM}

Hi James,

Thanks for your reply, when I execute the query

SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER'
EXCEPT
SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER_coded';

SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER_coded';
EXCEPT
SELECT name FROM sys.columns WHERE OBJECT_NAME(object_id) = 'CUSTOMER'

it gives nothing,it just shows the column ' name' null .

Thanks,

You mentioned Customer and Customer_coded are databases names not table names.

using the query above, you looking for table name called "customer" and "Customer_Coded", are these tables or databases. if they are databases, the above query will not work...

Hope it Helps!!

_{Tuesday, April 21, 2015 9:46 PM}

Ah, thanks Stan!! I didn't read the question carefully!! Venkatesh, use the code Stan had posted earlier. I had misunderstood and thought CUSTOMER and CUSTOMER_coded are two tables, in a database.

Share via

Need to find out missing columns .

Question

All replies (8)

Additional resources