Identify Unused SQL Server Tables

By:   |   Comments (15)   |   Related: More > Database Administration


Problem

I have a lot of tables in my database and I think some of them are not being used, how can I identify the unused tables in a SQL Server database?

Solution

As a DBA, it is a best practice to periodically review unused tables in your databases, in order to keep your production databases clean and contain only useful data and objects. These tables could be related to obsolete projects, temporary objects that were created or tables created for specific purposes and now they are no longer needed.

One way to identify unnecessary tables is by checking tables with a prefix or postfix of an underscore, TEMP or TMP in the name (as long as this is what is being done for objects that are temporary in nature). You can retrieve a list of all database tables using the sys.all_objects system table and restricting the type of the object to 'U' and then drop the unneeded tables.

This is the happy scenario, assuming that the system owners underscored these tables or added the prefix or postfix to the table to mark the tables as only being needed temporarily. But this is not always a valid method; it could be that the system owner missed renaming the tables, so we need a way to identify the tables that are no longer in use without depending on the naming convention.

To achieve this, we will use the sys.dm_db_index_usage_stats system DMV that stores the number of scans, seeks and updates performed on the database tables since the last time the SQL Service was restarted. We can build our query with the assumption that any table that has not been used since that time SQL Server was restarted will be considered as an unused table.

The last SQL Serve service restart time can be retrieved from the sys.dm_os_sys_info system DMV as below:

-- Get the Last SQL Service Restart
SELECT sqlserver_start_time  AS LastSQLServiceRestart FROM sys.dm_os_sys_info

Running the query will result in this output:

SQL Server Last Restart

To build our query that returns the unused tables, we will retrieve all the database tables first from the sys.all_objects system table, restrict the type of the object to 'U', where the table does not exist in the sys.dm_db_index_usage_stats system DMV; which indicates that no usage has been recorded on these tables since the last SQL Server service restart.

Additional useful information can be retrieved from the sys.all_objects system table, such as the table creation date and the last time a DDL statement was performed on this table.

It is beneficial to also join our query with the sys.dm_db_partition_stats system DMV to get the number of rows in these tables, which will help us in the decision of dropping these tables and make the information more meaningful in order to report it to the system owner.

The final query is as follows:

-- Create CTE for the unused tables, which are the tables from the sys.all_objects and 
-- not in the sys.dm_db_index_usage_stats table

; with UnUsedTables (TableName , TotalRowCount, CreatedDate , LastModifiedDate ) 
AS ( 
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
  FROM sys.all_objects  DBTable 
     JOIN sys.dm_db_partition_stats PS ON OBJECT_NAME(PS.object_id)=DBTable.name
  WHERE DBTable.type ='U' 
     AND NOT EXISTS (SELECT OBJECT_ID  
                     FROM sys.dm_db_index_usage_stats
                     WHERE OBJECT_ID = DBTable.object_id )
)
-- Select data from the CTE
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate 
FROM UnUsedTables
ORDER BY TotalRowCount ASC

The result after executing the query will be like below. Here we can see a list of tables that do not have any index usage stats, so therefore these are good candidates of unused tables. The result set includes the table name, the total rows in the table, the create date of the table and the last time the table structured was modified.

Unused SQL Server Tables

Summary / Precaution

The tables that are retrieved from the query are candidate tables and you should review the output with the system owner to make sure these tables are not needed before dropping the tables.

One suggestion is to rename the table with something like an underscore before actually dropping the table. This way if the table is needed you can rename the table back. If you run the above query again in a month or so and still see no usage then you can probably drop the table.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Comments For This Article




Wednesday, December 15, 2021 - 6:01:43 AM - SQLDBA Back To Top (89584)
hi SQL Server experts,

I have one question, i have a SQL Server databases with some tables, we have some separate user groups with roles and permissions, I need to get alert via DBmail stating loginame and access time and access table name if any one tried to access particular table without using Triggers can i achieve it like WMI event but it only allows create/drop/alter events only any suggestion would be highly appreciated

Wednesday, June 19, 2019 - 3:21:56 PM - Fernando Jacinto Back To Top (81519)

Maybe better to use this query adding SchemaName column just in case you have the same table name on more than 1 schema and ordering desc:

with UnUsedTables
AS 
( 
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
       ,s.name as SchemaName
  FROM sys.objects  DBTable 
  join sys.schemas s --select * from sys.schemas
  on DBTable.schema_id=s.schema_id
  INNER JOIN 
  (
    SELECT [object_id], row_count = SUM(row_count)
    FROM sys.dm_db_partition_stats PS  
    WHERE index_id IN (0,1)
    GROUP BY [object_id]
  ) AS PS
  ON PS.[object_id] = DBTable.[object_id]
  WHERE NOT EXISTS 
  (
    SELECT 1
    FROM sys.dm_db_index_usage_stats
    WHERE [object_id] = DBTable.[object_id] 
  )
)
SELECT DISTINCT SchemaName,TableName , TotalRowCount, CreatedDate , LastModifiedDate
FROM UnUsedTables where SchemaName <> 'sys'
ORDER BY TotalRowCount desc;

Monday, March 11, 2019 - 6:30:09 PM - DWalker07 Back To Top (79261)

Rather than using statistics, if your code does not build and execute dynamic SQL, the view sys.sql_expression_dependencies is reliable these days.

Friday, October 20, 2017 - 2:02:36 PM - Alex Back To Top (68575)

 Thanks for the query. I had to do a SELECT DISTINCT on the last portion of the query since it was giving me duplicate results.

A

SELECT DISTINCT TableName , TotalRowCount, CreatedDate , LastModifiedDate 

FROM UnUsedTables

ORDER BY UnUsedTables.LastModifiedDate DESC

 

 

Monday, March 7, 2016 - 1:26:39 PM - Ahmad Yaseen Back To Top (40879)

@Ray : Very nice method that you suggest, thank you for your share.

Thank you all for your input here and your nice comments

Best Regards,

Ahmad Yaseen

Monday, March 7, 2016 - 11:24:08 AM - Ray Back To Top (40877)

Nice Ahmad,

Rather than drop the table or simply rename it, I created a new schema I named zDrop (so it sorts to the bottom in Object Explorer) and move the table to that schema.  I do this so the developers are less likely to see the table and start using it again :).  It also removes any related database level objects (Constraints, Keys, Triggers) from the orginal schema name space so name collisions are less likely.

After I move the table to zDrop I also rename it by A) - pre-pending the orginal schema and B) appending the date in YYYYMMDD format.  So, the name looks like zDrop.<OrigininalSchema>_<OriginalName>_YYYYMMDD.  That way, if the table has to be restored I know where to put it. And I can check zDrop every month or so to drop objects that have been there more than 6 months.

I follow the same pattern with procedures, functions, and views.

 

Sunday, March 6, 2016 - 11:52:39 AM - Aaron Bertrand Back To Top (40871)

 

Pani, why do you think the script doesn't already include information about heaps? These are represented in both the index usage and partition stats DMVs (the heap itself is shown as index_id = 0).

Sunday, March 6, 2016 - 11:50:42 AM - Aaron Bertrand Back To Top (40870)

 

Richard, that is partially true.

When you reorganize an index, nothing gets changed in sys.dm_db_index_usage_stats.

When you update statistics manually, a system_scan is registered.

Currently, when you rebuild an index, the row gets completely removed from the DMV. This is a bug introduced in SQL Server 2012; see https://connect.microsoft.com/SQLServer/feedback/details/739566/rebuilding-an-index-clears-stats-from-sys-dm-db-index-usage-stats ... This *should* be registered as a *system* scan, rather than removing the row completely. This will be fixed in 2016 and possibly back-ported to 2014 SP1.

So, while sometimes the information may be missing due to a bug (or service restarts and other events that clear the DMV), index maintenance should not impact your ability to judge user interaction with the table. This is why most scripts you find focus on user_seeks, user_scans, etc. and ignore the system_ columns.

Saturday, March 5, 2016 - 5:42:36 AM - Richard Back To Top (40866)

 

Surely if index maintenance is in place to rebuild fragmented indexes then it will update the index usage stats, meaning this script will no longer see which tables are genuinely used by an application?

Thursday, March 3, 2016 - 8:24:08 AM - Pani Gorthi Back To Top (40850)

 Thanks for query.

 

How to find heap table update or modify data information.

 

Thursday, March 3, 2016 - 8:11:26 AM - Kevin Chant Back To Top (40849)

Clay, wouldn't you also want to check ones that have only had system_scans performed in sys.dm_db_index_usage_stats as well, as ones with only system_scans are potentially ones that have only been scanned to have their statistics updated?

Wednesday, March 2, 2016 - 12:16:36 PM - Aaron Bertrand Back To Top (40844)

 

Yes Clay, a table can only have one modify_date value. It is important to note, though, that that is the last time the table structure was modified, not the last time data in the table was updated.

Wednesday, March 2, 2016 - 12:02:59 PM - Clay Shannon Back To Top (40843)

 Thanks Betrand,

Would all the duplicated records be expected to be exactly the same (even as to LastModifiedDate)?

Wednesday, March 2, 2016 - 11:18:23 AM - Aaron Bertrand Back To Top (40842)

 

Clay, sys.dm_db_partition_stats will have multiple rows for any table that has more than one index (or a heap + 1 NC index), multiplied by the number of partitions. The query should filter on index_id (0,1) and sum the row count per partition. There are some other changes I'd suggest here, but to get it working as a start:

 

; with UnUsedTables
AS 
( 
  SELECT DBTable.name AS TableName
     ,PS.row_count AS TotalRowCount
     ,DBTable.create_date AS CreatedDate
     ,DBTable.modify_date AS LastModifiedDate
  FROM sys.objects  DBTable 
  INNER JOIN 
  (
    SELECT [object_id], row_count = SUM(row_count)
    FROM sys.dm_db_partition_stats PS  
    WHERE index_id IN (0,1)
    GROUP BY [object_id]
  ) AS PS
  ON PS.[object_id] = DBTable.[object_id]
  WHERE NOT EXISTS
  (
    SELECT 1
    FROM sys.dm_db_index_usage_stats
    WHERE [object_id] = DBTable.[object_id]
  )
)
SELECT TableName , TotalRowCount, CreatedDate , LastModifiedDate 
FROM UnUsedTables
ORDER BY TotalRowCount;

 

Also note that this query will exclude tables that have only had *system* seeks/scans/updates, so the NOT EXISTS may use another filter (like user_seeks+user_scans+user_updates > 0).

Wednesday, March 2, 2016 - 10:49:10 AM - Clay Shannon Back To Top (40841)

Thanks for these very handy queries.

With the one to show potentially droppable tables, I get a couple of instances where the exact same records are repeated (same table and all data appears mutliple times); this is only in a couple of cases out of hundreds, though... 














get free sql tips
agree to terms