Find ALL UNUSED columns on SQL DB Tables

Question

Hi there,

Is there a way in AZURE SQL Server to find all columns of a table which are NOT used by ANY SPs or Views.

I have 65000+ columns in DB based on information schema columns. I do see 200+ SPs of which not all columns are used. Some have select * FROM 300 COLUMN TABLE join on c1 but finally when its written to a table only 10 columns are used. Rest of the columns are just junk or full of nulls and not used by any joins or SP or view.

Is there any other approach?

Thanks

Answer 1

see this thread:

https://stackoverflow.com/questions/11769172/find-column-dependency

try (may miss some due to false positives in select dependencies):

SELECT 
	table_name, column_name
FROM INFORMATION_SCHEMA.COLUMNS c
where not exists
(
	select *
	from sys.sql_expression_dependencies d
	where OBJECT_NAME(d.referenced_id) = c.table_name
      AND OBJECT_DEFINITION (d.referencing_id)  LIKE '%' + c.column_name + '%'
)
order by 1,2

Answer 2

Hello RJ,

Thanks for posting your question in the Microsoft Q&A forum.

To identify unused columns in an Azure SQL Database, you can use a combination of dynamic management views (DMVs) and custom queries.

Use sys.dm_db_index_usage_stats to identify tables and indexes that haven't been accessed. This can help narrow down potentially unused columns

SELECT OBJECT_NAME(i.object_id) AS TableName, 
       i.name AS IndexName,
       u.user_seeks, u.user_scans, u.user_lookups, u.user_updates
FROM sys.indexes i
LEFT JOIN sys.dm_db_index_usage_stats u 
     ON i.object_id = u.object_id AND i.index_id = u.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsUserTable') = 1
ORDER BY u.user_seeks + u.user_scans + u.user_lookups DESC;

Create a query to check for NULL or empty values in varchar/nvarchar columns

SELECT 
    t.name AS TableName,
    c.name AS ColumnName,
    c.max_length,
    c.precision,
    c.scale,
    c.is_nullable,
    (SELECT COUNT(*) FROM [TableName] WHERE [ColumnName] IS NULL OR [ColumnName] = '') AS EmptyCount,
    (SELECT COUNT(*) FROM [TableName]) AS TotalCount
FROM sys.columns c
JOIN sys.tables t ON c.object_id = t.object_id
WHERE t.is_ms_shipped = 0
ORDER BY t.name, c.column_id;

Query sys.sql_modules to find columns referenced in SPs and views

SELECT DISTINCT
    o.name AS ObjectName,
    c.name AS ColumnName
FROM sys.sql_modules m
INNER JOIN sys.objects o ON m.object_id = o.object_id
CROSS APPLY sys.dm_sql_referenced_entities(o.schema_name + '.' + o.name, 'OBJECT') r
INNER JOIN sys.columns c ON r.referenced_id = c.object_id AND r.referenced_minor_id = c.column_id
WHERE o.type IN ('P', 'V') -- Stored Procedures and Views
ORDER BY o.name, c.name;

---

Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful

Answer 3

This is a major task, not the least is there are queries submitted from a client.

I have a shorter article on my web site Where Is That Table Used? which discusses various approaches to find reference to a table or a column, and I try to detail their strengths and weaknesses.