Identify unused tables of SQL databases

In this article, I am going to demonstrate how we can identify the unused tables of a given SQL Database. As a
database administrator, we must maintain the tables and objects of the SQL Database. In my organization, when we add
a new column or change the data type of a column, we export the data of the existing table in the backup table.
Often, we forgot to review and maintain those backup tables. So as a solution, we decided to create a SQL Job that
populates the list of unused tables and email the list to the DBA Team for review.

The best way to decommission any table is to rename it first, and if it does not break the functionality of the
application, we can drop the table and its dependencies. We decided that after a review of the unused table
completes; we will rename the tables and later drop them.

We decided to consider a table unused when user seek or user scan or user update was not performed on any index of the given table for two months. I have created a T-SQL stored
procedure, and it populates a list of tables and creates an HTML formatted email and send it to stack holders. It
uses the database name (@DatabaseName) as an input parameter. I have used the SQL Server Agent job
to that executes at 7 AM every Sunday.

The script uses the following DMVs to populate the details of the index and table usage, below is the list of the DMVs.

  1. Sys.schemas
  2. Sys.tables
  3. Sys.extended_properties
  4. sys.dm_db_index_usage_stats
  5. sys.indexes

The output of the script returns the following columns:

  • Note: I do not have any database that has a table whose index is not used for two months; therefore,
    I have made some changes in the WHERE clause of the script. The output returned by a script will show the list of
    tables that are not used for one day

To explain the script, I have divided it into three sections.

Populate the list of tables and modified date

In the first section, I have created a temporary table named #TableDetails. In that table,
we are going to insert the table name, schema name, last modified date, and time. To exclude the system tables, I
have used DMV named sys.extended_properties. The following code creates a table and a
dynamic T-SQL query that insert the data in #TableDetails table:

Populate the index usage

In the second section, I have created a temporary table named #Table_Usage. In that table,
we are going to insert the date of last user seek, date of the last user scan, and date of the last user update. The
following code creates a table and a dynamic T-SQL query that insert the data in
#Table_usage table:

Generate query output in HTML table format

In third section, I have consolidated the output of #TableDetails and
#Table_Usage to generate the required data in HTML format using XML. Below is the code:

The code of entire stored procedure looks like as following:

Create a SQL job to automate the process

Now, let us create a SQL Job. To do that, open SQL Server Management Studio Connect to the SQL
Server instance Expand SQL Server Agent Right-click on Jobs and select
Create New Jobs.

Create a SQL Job to populate the unused tables of SQL Database

On the New Job dialog box, provide the desired name of the job in Job Name textbox. Click on Steps (Screen 1) Click on New On New Job Step dialog
box (Screen 2), enter the desired name of the job step in Step name textbox. Select Transact-SQL script (T-SQL) from the Type drop-down box.
Enter the following T-SQL command in the command textbox and click OK to save the job step.

SQL Job step that executes a SP to populate the unused tables of SQL Database

To schedule the SQL Job, click on Schedule. Enter the desired name in
Name textbox. As mentioned, the job should run every week on Sunday at 7:00:00 AM;
therefore, select Recurring from Schedule type drop-down box.
Select Weekly from the Occurs drop-down box. Enter
07:00:00 in Occurs once at text box and click OK to save
the Schedule and close the dialog box.

New Job Schedule that executes a SP to populate the unused tables of SQL Database

Once the Schedule is configured, click OK to close the dialog box. To test this job, Expand SQL Server Agent
Right-click on the job and click on Start Job At Step. Once the SQL job completes
successfully, you should receive the email, as shown in the following image:

List of unused tables of SQL Database

Summary

In this article, we have learned how we can identify the list of unused tables of the SQL Database. Also, I have
explained how we can automate the execution of the script using the SQL Server Agent Job.

Nisarg Upadhyay
Latest posts by Nisarg Upadhyay (see all)

Author: admin

Leave a Reply

Your email address will not be published.