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.

Sys.schemas
Sys.tables
Sys.extended_properties
sys.dm_db_index_usage_stats
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:

declare @TableDetails_Query nvarchar(max)

if exists (select name from tempdb.sys.tables where name like '%#TableDetails%')

drop table #TableDetails

create table #TableDetails

(

[Database_Name] varchar(50)

,[Schema_Name] varchar(50)

,[Object_Id] bigint

,[Table_Name] varchar(500)

,[Modified_Date] datetime

)

set @TableDetails_Query =

'INSERT INTO #TableDetails (

[Database_Name]

,[Schema_Name]

,[Object_Id]

,[Table_Name]

,[Modified_Date]

)

SELECT DatabaseName

,SchemaName

,ObjectId

,TableName

,ModifiedDate

FROM (

SELECT DISTINCT ' + '''' + @DatabaseName + '''' + ' AS DatabaseName

,sch.NAME AS SchemaName

,tbl.object_id AS ObjectId

,tbl.NAME AS TableName

,tbl.modify_date AS ModifiedDate

FROM ' + @DatabaseName + '.sys.tables AS tbl

INNER JOIN ' + @DatabaseName + '.sys.schemas AS sch ON tbl.schema_id = sch.schema_id

LEFT JOIN ' + @DatabaseName + '.sys.extended_properties AS ep ON ep.major_id = tbl.[object_id] /*Exclude System Tables*/

WHERE tbl.NAME IS NOT NULL

AND sch.NAME IS NOT NULL

AND (ep.[name] IS NULL OR ep.[name] <> ''microsoft_database_tools_support'')

) AS rd

WHERE rd.SchemaName IS NOT NULL

ORDER BY DatabaseName ASC

,TableName ASC'

exec sp_Executesql @TableDetails_Query

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:

if exists (select name from tempdb.sys.tables where name like '%#Table_Usage%')

drop table #Table_Usage

CREATE TABLE #Table_Usage (

[Database_Name] varchar(150)

,[Object_Id] bigint

,[Table_Name] nvarchar(128) NULL

,[Last_User_Update] datetime NULL

,[Last_User_Seek] datetime NULL

,[Last_User_Scan] datetime NULL

,[Last_User_Lookup] datetime NULL

);

declare @TableUsage_Query nvarchar(max)

set @TableUsage_Query ='INSERT INTO #Table_Usage (

[Database_Name]

,[Object_Id]

,[Table_Name]

,[Last_User_Update]

,[Last_User_Seek]

,[Last_User_Scan]

,[Last_User_Lookup]

)

SELECT DatabaseName

,ObjectId

,TableName

,LastUserUpdate

,LastUserSeek

,LastUserScan

,LastUserLookup

FROM (

SELECT

' + '''' + @DatabaseName + '''' + ' AS DatabaseName

,indexusage.OBJECT_ID AS ObjectId

,obj.NAME AS TableName

,indexusage.last_user_update AS LastUserUpdate

,indexusage.last_user_seek AS LastUserSeek

,indexusage.last_user_scan AS LastUserScan

,indexusage.last_user_lookup AS LastUserLookup

FROM ' + @DatabaseName + '.sys.dm_db_index_usage_stats AS indexusage

INNER JOIN ' + @DatabaseName + '.sys.objects AS obj ON

indexusage.OBJECT_ID = obj.OBJECT_ID

AND o.NAME IS NOT NULL)as tbl'

exec sp_Executesql @TableUsage_Query

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:

declare @tableHTML nvarchar(max)

declare @Subject nvarchar(max)

set @Subject='List of Unused tables on '+ @DatabaseName

SET @tableHTML =

<html>

<Body>

table {font-size:9.0pt;font-family:verdana;text-align:left;}

tr {text-align:left;}

h3 {

display: block;

font-size: 15.0pt;

font-weight: bold;

font-family: verdana;

text-align:left;

}

</style>

<H4>List of unused tables on ' +@DatabaseName + '</H4>' +

N'<table border="1">' +

N'<tr>

<th>Database Name</th>

<th>Schema Name</th>

<th>Table Name</th>

<th>Table Modified Date</th>

<th>Last User Seek Occured on</th>

<th>Last User Scan Occured on</th>

<th>Last User Update Occured on</th>

</tr>' +

CAST((SELECT

ISNULL(tableDetails.Database_Name, '') AS 'TD'

,''

,ISNULL(tableDetails.[Schema_Name], '') AS 'TD'

,''

,ISNULL(tableDetails.[Table_Name] , '') AS 'TD'

,''

,ISNULL(tableDetails.[Modified_Date] , '') AS 'TD'

,''

,ISNULL(tableusage.[Last_User_Seek] , '') AS 'TD'

,''

,ISNULL(tableusage.[Last_User_Scan] , '') AS 'TD'

,''

,ISNULL(tableusage.[Last_User_Update] , '') AS 'TD'

,''

FROM #Table_Usage AS tableusage

INNER JOIN #TableDetails AS TableDetails ON tableusage.Object_Id = TableDetails.Object_Id

WHERE

Last_User_seek <= dateadd(Month, -2, getdate()) AND

Last_User_Scan <= dateadd(Month, -2, getdate()) AND

Last_User_Update <= dateadd(Month, -2, getdate())

Order by tableDetails.[Schema_Name] ASC

,tableDetails.[Table_Name] ASC

FOR XML PATH ('tr'), ROOT)

AS NVARCHAR(MAX)) +

N'</table>

</html>

</Body>'

/*Send Email*/

EXEC msdb..sp_send_dbmail @profile_name = 'OutlookMail'

,@recipients = 'ni***g******@outlook.com'

,@subject = @subject

,@importance = 'High'

,@body = @tableHTML

,@body_format = 'HTML';

The code of entire stored procedure looks like as following:

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

alter procedure sp_getunused_tables

@DatabaseName varchar(50)

begin

/*Insert details of the tables */

declare @TableDetails_Query nvarchar(max)

if exists (select name from tempdb.sys.tables where name like '%#TableDetails%')

drop table #TableDetails

create table #TableDetails

(

[Database_Name] varchar(50)

,[Schema_Name] varchar(50)

,[Object_Id] bigint

,[Table_Name] varchar(500)

,[Modified_Date] datetime

)

set @TableDetails_Query =

'INSERT INTO #TableDetails (

[Database_Name]

,[Schema_Name]

,[Object_Id]

,[Table_Name]

,[Modified_Date]

)

SELECT DatabaseName

,SchemaName

,ObjectId

,TableName

,ModifiedDate

FROM (

SELECT DISTINCT ' + '''' + @DatabaseName + '''' + ' AS DatabaseName

,sch.NAME AS SchemaName

,tbl.object_id AS ObjectId

,tbl.NAME AS TableName

,tbl.modify_date AS ModifiedDate

FROM ' + @DatabaseName + '.sys.tables AS tbl

INNER JOIN ' + @DatabaseName + '.sys.schemas AS sch ON tbl.schema_id = sch.schema_id

LEFT JOIN ' + @DatabaseName + '.sys.extended_properties AS ep ON ep.major_id = tbl.[object_id] /*Exclude System Tables*/

WHERE tbl.NAME IS NOT NULL

AND sch.NAME IS NOT NULL

AND (ep.[name] IS NULL OR ep.[name] <> ''microsoft_database_tools_support'')

) AS rd

WHERE rd.SchemaName IS NOT NULL

ORDER BY DatabaseName ASC

,TableName ASC'

exec sp_Executesql @TableDetails_Query

/*Insert Index usage*/

if exists (select name from tempdb.sys.tables where name like '%#Table_Usage%')

drop table #Table_Usage

CREATE TABLE #Table_Usage (

[Database_Name] varchar(150)

,[Object_Id] bigint

,[Table_Name] nvarchar(128) NULL

,[Last_User_Update] datetime NULL

,[Last_User_Seek] datetime NULL

,[Last_User_Scan] datetime NULL

,[Last_User_Lookup] datetime NULL

);

declare @TableUsage_Query nvarchar(max)

set @TableUsage_Query ='INSERT INTO #Table_Usage (

[Database_Name]

,[Object_Id]

,[Table_Name]

,[Last_User_Update]

,[Last_User_Seek]

,[Last_User_Scan]

,[Last_User_Lookup]

)

SELECT DatabaseName

,ObjectId

,TableName

,LastUserUpdate

,LastUserSeek

,LastUserScan

,LastUserLookup

FROM (

SELECT

' + '''' + @DatabaseName + '''' + ' AS DatabaseName

,indexusage.OBJECT_ID AS ObjectId

,obj.NAME AS TableName

,indexusage.last_user_update AS LastUserUpdate

,indexusage.last_user_seek AS LastUserSeek

,indexusage.last_user_scan AS LastUserScan

,indexusage.last_user_lookup AS LastUserLookup

FROM ' + @DatabaseName + '.sys.dm_db_index_usage_stats AS indexusage

INNER JOIN ' + @DatabaseName + '.sys.objects AS obj ON

indexusage.OBJECT_ID = obj.OBJECT_ID

AND obj.NAME IS NOT NULL

)as tbl'

exec sp_Executesql @TableUsage_Query

declare @tableHTML nvarchar(max)

declare @Subject nvarchar(max)

set @Subject='List of Unused tables on '+ @DatabaseName

SET @tableHTML =

<html>

<Body>

table {font-size:9.0pt;font-family:verdana;text-align:left;}

tr {text-align:left;}

h3 {

display: block;

font-size: 15.0pt;

font-weight: bold;

font-family: verdana;

text-align:left;

}

</style>

<H4>List of unused tables on ' +@DatabaseName + '</H4>' +

N'<table border="1">' +

N'<tr>

<th>Database Name</th>

<th>Schema Name</th>

<th>Table Name</th>

<th>Table Modified Date</th>

<th>Last User Seek Occured on</th>

<th>Last User Scan Occured on</th>

<th>Last User Update Occured on</th>

</tr>' +

CAST((SELECT

ISNULL(tableDetails.Database_Name, '') AS 'TD'

,''

,ISNULL(tableDetails.[Schema_Name], '') AS 'TD'

,''

,ISNULL(tableDetails.[Table_Name] , '') AS 'TD'

,''

,ISNULL(tableDetails.[Modified_Date] , '') AS 'TD'

,''

,ISNULL(tableusage.[Last_User_Seek] , '') AS 'TD'

,''

,ISNULL(tableusage.[Last_User_Scan] , '') AS 'TD'

,''

,ISNULL(tableusage.[Last_User_Update] , '') AS 'TD'

,''

FROM #Table_Usage AS tableusage

INNER JOIN #TableDetails AS TableDetails ON tableusage.Object_Id = TableDetails.Object_Id

WHERE

Last_User_seek <= dateadd(day, 0, getdate()) AND

Last_User_Scan <= dateadd(day, 0, getdate()) AND

Last_User_Update <= dateadd(day, 0, getdate())

Order by tableDetails.[Schema_Name] ASC

,tableDetails.[Table_Name] ASC

FOR XML PATH ('tr'), ROOT)

AS NVARCHAR(MAX)) +

N'</table>

</html>

</Body>'

/*Send Email*/

EXEC msdb..sp_send_dbmail @profile_name = 'OutlookMail'

,@recipients = 'ni**********87@outlook.com'

,@subject = @subject

,@importance = 'High'

,@body = @tableHTML

,@body_format = 'HTML';

End

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

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.

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.

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:

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.

Author
Recent Posts

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

Latest posts by Nisarg Upadhyay (see all)

Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
Changing the location of FILESTREAM data files in SQL Database - October 14, 2022

Populate the list of tables and modified date

Populate the index usage

Generate query output in HTML table format

Create a SQL job to automate the process

Summary

Related posts: