Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Identify Unused SQL Server Tables


By:   |   Last Updated: 2016-03-02   |   Comments (13)   |   Related Tips: 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


Last Updated: 2016-03-02


next webcast button


next tip button



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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

 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 07, 2016 - 1:26:39 PM - Ahmad Yaseen Back To Top

@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 07, 2016 - 11:24:08 AM - Ray Back To Top

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 06, 2016 - 11:52:39 AM - Aaron Bertrand Back To Top

 

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 06, 2016 - 11:50:42 AM - Aaron Bertrand Back To Top

 

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 05, 2016 - 5:42:36 AM - Richard Back To Top

 

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 03, 2016 - 8:24:08 AM - Pani Gorthi Back To Top

 Thanks for query.

 

How to find heap table update or modify data information.

 


Thursday, March 03, 2016 - 8:11:26 AM - Kevin Chant Back To Top

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 02, 2016 - 12:16:36 PM - Aaron Bertrand Back To Top

 

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 02, 2016 - 12:02:59 PM - Clay Shannon Back To Top

 Thanks Betrand,

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


Wednesday, March 02, 2016 - 11:18:23 AM - Aaron Bertrand Back To Top

 

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 02, 2016 - 10:49:10 AM - Clay Shannon Back To Top

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... 


Learn more about SQL Server tools