Finding Unused Tables And Procedures

Occasionally, it’s possible for us in the world of database administration to clean up some databases, removing old and unused tables or stored procedures.  When the developers can’t tell you which tables and procedures they aren’t using any longer—or the database simply has too many tables and procedures to determine that easily—we fall back to the next-best thing:  checking access.  The following procedures will let you see which tables and procedures have not been accessed since the last time the server restarted.

First, to get the server uptime, I tend to run the following query:

select 
	datepart(year, CURRENT_TIMESTAMP - create_date)-1900 as years,
	datepart(month, CURRENT_TIMESTAMP - create_date) - 1 as months,
	datepart(day, CURRENT_TIMESTAMP - create_date) - 1 as days,
	datepart(hour, CURRENT_TIMESTAMP - create_date) as hours,
	datepart(minute, CURRENT_TIMESTAMP - create_date) as minutes,
	datepart(second, CURRENT_TIMESTAMP - create_date) as seconds
from sys.databases
where name = 'tempdb';

If the server just rebooted, you won’t get the same results as if the thing had been running for the past several months.

Next, let’s check table access since the last reboot:

declare @AccessCutoff int = 10;

SELECT 
	SCHEMA_NAME(so.schema_id) AS SchemaName,
	OBJECT_NAME(ius.object_id) AS TableName,
	SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS TimesAccessed 
FROM 
	sys.indexes i
	INNER JOIN sys.dm_db_index_usage_stats ius
		ON ius.object_id = i.object_id
		AND ius.index_id = i.index_id	
	INNER JOIN sys.objects so on ius.object_id = so.object_id	
WHERE
	ius.database_id = DB_ID()
	AND DB_NAME(ius.database_id) = DB_NAME(DB_ID())
GROUP BY 
	SCHEMA_NAME(so.schema_id),
	OBJECT_NAME(ius.object_id)
HAVING
	SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) <= @AccessCutoff
ORDER BY 
	TimesAccessed ASC;

It’s important to note that your regular maintenance routines might access these tables automatically, so just because the TimesAccessed is greater than 0 doesn’t mean the table is really in use.  This is why I added in a variable to limit the TimesAccessed to some number; in certain cases, I might be able to get away with 0, but other times, I will need to push that up to some non-zero number like 10 or 20.

Finally, let’s look at stored procedures which have not been used lately.  I’m adapting code that Derek Dieter wrote to find highly-active stored procedures and doing the opposite:  looking for procedures which are not in the cache.  Here is my query:

with executedprocedures as
(	
	SELECT 
		OBJECT_SCHEMA_NAME(st.objectid,dbid) as SchemaName,
		OBJECT_NAME(st.objectid,dbid) as StoredProcedure
	FROM 
		sys.dm_exec_cached_plans cp  
		CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) st  
	WHERE 
		DB_NAME(st.dbid) IS NOT NULL 
		AND cp.objtype = 'proc' 
)
select object_schema_name(object_id) as SchemaName, name as TableName from sys.procedures
except
select SchemaName, StoredProcedure from executedprocedures;

It is vital to note that there may be some tables and procedures which only get accessed periodically.  Thus, even if you have a solid uptime result in the first query, you probably don’t want to create a script to take the results and automatically drop tables or procedures.  Instead, take the results as the first phase of your investigation.  With this technique, you might turn a needle in a haystack search into…well, a needle in a smaller haystack…