-4

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.

ColumnName Tables Count
ProductSerialNo Product,ProductModels,Inventory 3
EquipmentNo Equipment,Warehouse 2
Heinzi
  • 167,459
  • 57
  • 363
  • 519
Montreal
  • 15
  • 8
  • Show us the code you have tried and what part of it you are stuck with – squillman Sep 12 '22 at 20:36
  • How do I mark my own answer as solved? lol :) – Montreal Sep 12 '22 at 22:26
  • @Montreal: Put it into an answer box (rather than into the question) and then accept your own answer. That is not only allowed, but actually [actively encouraged](https://stackoverflow.com/help/self-answer). – Heinzi Sep 13 '22 at 13:50

2 Answers2

0

  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

All of the above combined yields (fiddle):

SELECT column_name, STRING_AGG(table_name, ', ') AS tables, COUNT(*) AS count
  FROM information_schema.columns
 GROUP BY column_name
 ORDER BY COUNT(*) DESC
Heinzi
  • 167,459
  • 57
  • 363
  • 519
  • 1
    @Montreal: That would diminish the educational value of this exercise: I want to help you become a better developer, not do your work for free. After reading the documentation linked to in my answer, where exactly are you stuck? – Heinzi Sep 12 '22 at 21:09
  • I am an SQL expert and can solve the problem million different ways. As I have been super busy at work, thought I could ask for help from the community in here and perhaps help others who have the same problem. Thereby contributing to the knowledge base of stackoverflow. String_Agg does not work in this situation. Thank you anyways! :) – Montreal Sep 12 '22 at 21:57
  • @Montreal: I see. Well, StackOverflow is primarily meant as a platform to get expert advice if you *need knowledge*, not as a means to get others to do grunt work for you. That's a common misconception, though, so I don't blame you. We're all super-busy. :-) – Heinzi Sep 13 '22 at 13:49
  • @Montreal: BTW, I do think that STRING_AGG should work here. Unfortunately, sqlfiddle is currently down, but I'll try to create an example when it's back up. – Heinzi Sep 13 '22 at 13:51
  • @Montreal: String_agg works fine for me (see updated answer). – Heinzi Sep 14 '22 at 07:55
0

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! :)

Montreal
  • 15
  • 8