Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Checking columns in two similar tables Expand / Collapse
Author
Message
Posted Friday, January 03, 2014 2:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:56 AM
Points: 8, Visits: 43
Can u help me with this query?

For every table in my database there is a duplicate table with same columns. For example, employee is the name of main table, there is employee_dup table in same database.
There is only one column extra in _dup tables i.e.,idn column.
Now, I want to know all the columns present in main table which are not present in corresponding _dup table. There might be a chance of missing one or two columns in _dup tables. So i want a query to find out all the columns present in main table that are not present in hx table.

Thanks in advance..,

Gautham
Post #1527388
Posted Friday, January 03, 2014 2:14 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:58 AM
Points: 5,077, Visits: 8,918
Gautham

First you said that the _dup table has the same columns plus an ID column, then you said that some columns may be missing. Do I take it you mean that the _dup table should have the same columns plus an ID column?

All you need to do to solve this is query INFORMATION_SCHEMA.COLUMNS to find columns in a table, then again to find columns in the corresponding _dup table. Then outer join the two result sets together to identify the columns that appear in one but not the other. Have a go at that, and please post back if you get stuck.

John
Post #1527391
Posted Friday, January 03, 2014 2:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:56 AM
Points: 8, Visits: 43
_dup tables should have all the columns in main table plus idn column.
But while creating _dup tables, there is a chance I may have forgotten some columns in main tables. So,I want to find those columns which are in main table but are not present in _dup table.
I have cracked this query upto some extent but there is something missing in this. Can u crack it?

select *
from
(
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%_dup'
) as X
left join
(
select a.TABLE_NAME,a.COLUMN_NAME,b.TABLE_NAME as TABLE_NAME_DUP,b.COLUMN_NAME as COLUMN_NAME_DUP
FROM
(
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME not like '%_DUP'
) AS a
inner join
(
select table_name,column_name from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME like '%_DUP'
) as b
on A.TABLE_NAME+'_DUP'=B.TABLE_NAME and A.COLUMN_NAME=B.COLUMN_NAME
) as Y
on X.TABLE_NAME=Y.TABLE_NAME_DUP AND Y.COLUMN_NAME_DUP is NULL
Post #1527393
Posted Friday, January 03, 2014 2:31 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:58 AM
Points: 5,077, Visits: 8,918
I have cracked this query upto some extent but there is something missing in this.

What is missing? Don't forget I can't see your screen.

I think you've made it more complicated than it needs to be. Something like this should do it:

SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT JOIN INFORMATION_SCHEMA.COLUMNS cdup
ON c.TABLE_NAME + '_dup' = cdup.TABLE_NAME AND c.COLUMN_NAME = cdup.COLUMN_NAME
WHERE cdup.COLUMN_NAME IS NULL

Beware if you have tables in more than one schema - you'll have to include the TABLE_SCHEMA column in your join as well.

John
Post #1527398
Posted Friday, January 03, 2014 2:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 14, 2014 12:56 AM
Points: 8, Visits: 43
John,
Thanks for ur query but I am not getting the exact answer with that.
Let me explain what my query does.

Superset: All _dup table names and column names from information_schema.columns.

Subset :1)All _dup table names and column names
2)All main table names and column names
Inner joining both 1) and 2) on condition table_name=table_name+'_dup'

Now, I want to subtract subset from superset.
So, I used left join which gets all the matched records and unmatched records in Superset.
Now I want to remove all the matched records so that I m left with unmatched records which are the column names in main table but not present in _dup.
Post #1527404
Posted Friday, January 03, 2014 2:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:58 AM
Points: 5,077, Visits: 8,918
Like I said, I can't see your screen. What is wrong with the results your query produces, and what is wrong with those my query does? Maybe you could post some CREATE TABLE statements so that I can do some testing, please?

John
Post #1527406
Posted Friday, January 03, 2014 4:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:26 PM
Points: 5,961, Visits: 12,847
gautham.gn (1/3/2014)
I want to find those columns which are in main table but are not present in _dup table.

Try this
SELECT			c.TABLE_SCHEMA
, c.TABLE_NAME
, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
LEFT OUTER JOIN INFORMATION_SCHEMA.COLUMNS cd
ON c.TABLE_SCHEMA = cd.TABLE_SCHEMA AND c.TABLE_NAME = cd.TABLE_NAME
AND c.COLUMN_NAME = cd.COLUMN_NAME
WHERE cd.TABLE_NAME like '%_dup' and cd.COLUMN_NAME IS NULL
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs"
Post #1527440
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse