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.

Is there any way to compare every table with each other in a database, listing the number of common columns between each tables? I prefer using join over subquery and information_schema.columns over sys.columns, if possible.

For example, if I have 4 tables, I'd expect output like this:

list1        list2        commonColumns  
-----       --------     --------------------
table1     table2         1
table1     table3         2
table1     table4         0
table2     table1         1
table2     table3         5
table2     table4         2
table3     table1         3 
table3     table2         0
table3     table4         1
table4     table1         2
table4     table2         3
table4     table3         0
share|improve this question
    
Why would you prefer INFORMATION_SCHEMA over sys.columns? –  Aaron Bertrand Jun 20 '13 at 4:25
    
stackoverflow.com/a/4381789/2503214 –  sqlsean Jun 20 '13 at 4:28
    
And did you read my link? If you're sticking with SQL Server, INFORMATION_SCHEMA will do you no favors, and it will become increasingly less reliable as you move to new versions and new features are added... –  Aaron Bertrand Jun 20 '13 at 4:31
    
Note also that the answer there compared INFORMATION_SCHEMA to sysobjects (an old, backward-compatible system table from SQL Server 2000), not to sys.objects (a newer catalog view). –  Aaron Bertrand Jun 20 '13 at 4:33
    
may be you are right but sys.columns only gives me column names. how do I get the table name it belongs to? –  sqlsean Jun 20 '13 at 4:45

1 Answer 1

up vote 1 down vote accepted

You probably want schema name along with table name, and here is a way to get the information from the proper catalog views instead of INFORMATION_SCHEMA (more on why here).

;WITH c AS 
(
  SELECT 
    t = QUOTENAME(OBJECT_SCHEMA_NAME([object_id])) 
      + '.' + QUOTENAME(OBJECT_NAME([object_id])), 
    name
  FROM sys.columns
  WHERE OBJECTPROPERTY([object_id], 'IsMsShipped') = 0
)
SELECT 
   list1 = c1.t, 
   list2 = c2.t, 
   commonColumns = COUNT(CASE WHEN c1.name = c2.name THEN 1 END)
 FROM c AS c1
 INNER JOIN c AS c2
 ON c1.t < c2.t
 GROUP BY c1.t, c2.t
 ORDER BY c1.t, c2.t;

SQLFiddle demo

share|improve this answer
    
Exactly what i was looking for. thank you Aaron. looking at your blog very informational. –  sqlsean Jun 20 '13 at 5:13

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.