Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

idea is to list all the tables that has redundant columns across database using information_schema.columns! let's say

table1 = dbo.ABC columns = id, name, department, designation

table2= dbo.DEF columns= department, permissions, cube

table3= dbo.GHI columns= street, zip, city, state

the query should list the tables 'dbo.ABC' and 'dbo.DEF'

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

something like this:

select distinct c1.table_schema||'.'||c1.table_name
from information_schema.columns c1
where exists (select 1
              from information_schema.columns c2
              where c2.table_name <> c1.table_name
                and c2.column_name = c1.column_name
                and c2.table_schema = c1.table_schema);
share|improve this answer
    
thank you for your effort. your query works but there is a slight change on the requirement. instead of jus listing, is there any way to compare each and every table in the data base and generate an additional column which will list the number of common column between each tables!! ?? please use join if possible thx. example: (table1)ABC (table2)DEF (commonColumns)1 –  sqlsean Jun 20 '13 at 3:14
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.