question

NeophyteSQL avatar image
0 Votes"
NeophyteSQL asked ·

how to identify tables that are not being accessed by the application or used

how to identify unused tables on prod so they can be dropped if the table had index, will user updates column on the index usage stats give information what if the index never gets used and only non clustered indexes exist

sql-server-general
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

GuoxiongYuan-7218 avatar image
1 Vote"
GuoxiongYuan-7218 answered ·

You may try to use the system view sys.dm_db_index_usage_stats to check the columns last_user_seek, last_user_scan, last_user_lookup and last_user_updates:

 SELECT o.name AS Table_Name, s.* 
 FROM sys.dm_db_index_usage_stats AS s
 INNER JOIN sys.databases AS d ON d.database_id = s.database_id
 INNER JOIN sys.objects AS o ON o.object_id = s.object_id
 WHERE d.name = 'DATABASE_NAME' AND o.type = 'U';

Please note that the tracking data will be reset after the SQL server restarts.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Criszhan-msft avatar image
0 Votes"
Criszhan-msft answered ·

Hi,

Please refer to the script in the following article.
https://www.mssqltips.com/sqlservertip/4191/identify-unused-sql-server-tables/

will user updates column on the index usage stats give information what if the index never gets used and only non clustered indexes exist

The system view sys.dm_db_index_usage_stats will returns counts of different types of index(HEAP,NONCLUSTERED, CLUSTERED)operations and the time each type of operation was last performed.

· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi,
Is there any update on this case?
If the answer is helpful, please click "Accept Answer" and upvote it.

0 Votes 0 ·