Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Return a list columns in the database and for each column a comma delimited list of tables in which that column appears
/* Return a list columns in the database and for each column a comma delimited list of tables in which that column appears */
SELECT
c.name AS ColumnName
, STRING_AGG(CONCAT( QUOTENNAME(s.name), '.', QUOTENAME(t.name)), ', ')
WITHIN GROUP (ORDER BY CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name))) AS TableListForColumn
, COUNT(*) AS TableUsageCountForColumn
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY c.name
ORDER BY TableUsageCountForColumn DESC
, ColumnName ASC;
/* Return a list of tables in a database and for each table a comma delimited list of columns in that table */
SELECT
CONCAT(QUOTENAME(s.name), '.', QUOTENAME(t.name)) AS TableName
, STRING_AGG(c.name, ', ' )
WITHIN GROUP (ORDER BY c.name) AS ColumnList
, COUNT(*) AS CountColumnsInTable
FROM sys.tables AS t
JOIN sys.columns AS c ON c.object_id = t.object_id
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
GROUP BY concat(quotename(s.name), '.', quotename(t.name))
ORDER BY CountColumnsInTable DESC
, TableName ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment