I would like to create a matrix for all the common columns used across multiple tables in a database sorted in descending order. For example ProductSerialNo is used in three tables.

SOLUTION:

select distinct A.COLUMN_NAME,
  STUFF((SELECT distinct ', '   B.TABLE_NAME
         from INFORMATION_SCHEMA.COLUMNS B
         where A.COLUMN_NAME = B.COLUMN_NAME
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,2,'') AS [TABLE_NAMES]
        , X.COUNT
from INFORMATION_SCHEMA.COLUMNS A
LEFT JOIN 
(
select COLUMN_NAME, COUNT(1) [COUNT]
from INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
) X ON X.COLUMN_NAME = A.COLUMN_NAME
ORDER BY X.COUNT DESC

Thank you @Taryn for his answer How to get column values in one comma separated value

CodePudding user response：

1. As a data source you can use INFORMATION_SCHEMA.COLUMNS, or you can join sys.columns and sys.tables.

2. To just get the number of tables, COUNT(*) with GROUP BY will suffice.

3. To get the list of comma-separated tables, use STRING_AGG in SQL Server 2017 or higher. If you have an older version of SQL Server, workarounds can be found in this related question:

   SQL group_concat function in SQL Server

CodePudding user response：

SOLUTION:

select distinct A.COLUMN_NAME,
  STUFF((SELECT distinct ', '   B.TABLE_NAME
         from INFORMATION_SCHEMA.COLUMNS B
         where A.COLUMN_NAME = B.COLUMN_NAME
            FOR XML PATH(''), TYPE
            ).value('.', 'VARCHAR(MAX)') 
        ,1,2,'') AS [TABLE_NAMES]
        , X.COUNT
from INFORMATION_SCHEMA.COLUMNS A
LEFT JOIN 
(
select COLUMN_NAME, COUNT(1) [COUNT]
from INFORMATION_SCHEMA.COLUMNS
GROUP BY COLUMN_NAME
) X ON X.COLUMN_NAME = A.COLUMN_NAME
ORDER BY X.COUNT DESC

Thank you everyone! :)