By: Ahmad Yaseen | Last Updated: 2016-03-02 | Comments (13) | Database Administration
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?
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:
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.
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.
- Check different ways to Find Last Restart of SQL Server
- Read more about sys.all_objects
- Read more about sys.dm_db_index_usage_stats
- Read more about sys.dm_db_partition_stats
Last Updated: 2016-03-02
About the author
View all my tips