SQL Server Finding Unused Tables

On

On the last post I put out, I showed a script on finding the indexes that weren't used. As with most of my posts I'm usually just posting things I'm working on at work. After running the script it became clear that there we're entire tables that were no longer being used, so I decided to modify the script to find those tables. It's pretty much the same, except for one clause which specifies to find indexid's of 0 or 1 (Heap or Clustered). You can see this in the script below. Truth is you can take this type of information and turn it however you want. I think I've got two more scripts to publish focusing on the DMV sys.dm_db_index_usage_stats, which I need to write over the next few train trips to work and back.

--Navigate to DB to execute

Declare @Threshold int

Declare @DBName varchar(100)

Set @Threshold = 0

Set @DBName =
'userdb'

Create
Table #AllIndexes

(TableName varchar(100),

IndexName varchar(100),

indexID int,

ObjectID int)

Create
Table #IndexUsage

(

object_id
int,

index_id bigint,

user_seeks bigint,

user_scans bigint,

user_lookups bigint,

user_updates bigint,

last_user_seek datetime,

last_user_scan datetime,

last_user_lookup datetime,

last_user_update datetime)

insert
into #IndexUsage(object_id,index_id,user_seeks,

user_scans,user_lookups,user_updates,last_user_seek,

last_user_scan,last_user_lookup,last_user_update)

Select
object_id,index_id,user_seeks,

user_scans,user_lookups,user_updates,last_user_seek,

last_user_scan,last_user_lookup,last_user_update

from
sys.dm_db_index_usage_stats

where database_id =
db_id(@DBName)

insert
into #AllIndexes

select t.name, i.name, i.index_id, t.object_id

from
sys.indexes i

inner
join
sys.tables t

on

i.object_id
= t.object_id

select
*
from #AllIndexes ai

left
join #IndexUsage iu

on

ai.indexid = iu.index_id

and

ai.objectid = iu.object_id

where
(user_seeks is
null
or user_seeks <= @Threshold)

and
(user_scans is
null
or user_scans <= @Threshold)

and
(user_lookups is
null
or user_lookups <= @Threshold)

and Indexid in
(0,1)

order
by user_updates desc, TableName, IndexID