Count number of common column names among all tables in a database 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
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 &lt; c2.t
 GROUP BY c1.t, c2.t
 ORDER BY c1.t, c2.t;


SQLFiddle demo