How to find unused tables in SQL Server

Question

Is there any way to find out when the data was last entered into the table? I am trying to find outdated tables in my database and would like to know if there is simple script (s) that I can run?

Answer 1

You can try to check the results of the sys.dm_db_index_usage_stats dynamic control view as follows:

SELECT * FROM sys.dm_db_index_usage_stats WHERE [database_id] = DB_ID() AND [object_id] = OBJECT_ID('TableName') 

This will return things like last_user_seek dates, validation and updating indexes in the table.

Howvever, beware, as statistics to represent dynamic control reset when the server restarts. The longer the server runs, the more confidence you can have if there is no activity in the reports.

I personally would also check all the source code to check the links to the table in question, and also look for all sprocs / UDF for links (you can use SQL Search from Red Gate to do this - for free)

Answer 2

If someone is looking for all unused tables in the database (more header request than question), this guy had a good query for entering all immutable tables into the database. In this case, "unchanged" - any table without writing to sys.dm_db_index_usage_stats (again, as with AdaTheDev's answer, only after the last sql server is rebooted).

 SELECT OBJECTNAME = Object_name(I.object_id), INDEXNAME = I.name, I.index_id FROM sys.indexes AS I INNER JOIN sys.objects AS O ON I.object_id = O.object_id WHERE Objectproperty(O.object_id, 'IsUserTable') = 1 AND I.index_id NOT IN (SELECT S.index_id FROM sys.dm_db_index_usage_stats AS S WHERE S.object_id = I.object_id AND I.index_id = S.index_id AND database_id = Db_id(Db_name())) ORDER BY objectname, I.index_id, indexname ASC 

Answer 3

If this is important for your applications and / or companies, and the tables were designed correctly, then each table should have a column called "LastModifiedTime". You can query this table to determine which tables are out of date.