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

Identify unused tables of SQL databases

September 24, 2020 by

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:

Column Name

Dynamic Management View to populate the information

Description

SchemaName

dbo.sys.schemas

Name of the schema

TableName

dbo.sys.tables

Name of the table

TableModifiedDate

dbo.sys.tables

The date and time of change in table schema (column added or renamed or data type change)

Last_user_scan

sys.dm_db_index_usage_stats

Date when the last index scan was performed by a query

Last_user_lookup

sys.dm_db_index_usage_stats

Date of the last index lookup performed by a query

Last_user_seek

sys.dm_db_index_usage_stats

Date of the last index seek performed by a query

Last_user_update

sys.dm_db_index_usage_stats

Date of the last index update performed by a query

  • 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
DBAtools, Jobs, SQL commands, SQLCMD

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

168 Views