January 3, 2014 at 2:04 am
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
January 3, 2014 at 2:14 am
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
January 3, 2014 at 2:20 am
_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
January 3, 2014 at 2:31 am
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
January 3, 2014 at 2:42 am
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.
January 3, 2014 at 2:46 am
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
January 3, 2014 at 4:12 am
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
SELECTc.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" 😉
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply