What Tables Are Wasted?

Wasted space still seems to be on everyone's mind to some extent. I've wondered what tables do we use? We have our offenders, those are easy to track. What about that little database that's been around for 4 years that no one left even really knows what it does? I have a set of scripts that may help some with that. I'll include a download link as well.

It's listed in the comments, I'll mention it here as well. This list is only accurate to the last reboot of services. Do not base your delete decisions on this solely. If you track it over time, it should be reviewed to see about removing, then only remove it with a good backup so you can restore it if needed. Take caution any time you remove parts of your database.

You can download the full script Here. Any comments or suggestions are appreciated. Thanks again!

/*
Author Bill Barnes
Created 01/10/2014

Use: The purpose of this script is to show indexes and tables that have not been used
since the last restart of SQL services. This script can be quickly modified to show what
tables have been used and provide more useful data. This will ignore all system schema
based tables.

Note: an update or scan is counted per instance set not per scan. If you update a row once
you get one addition to update. If you update a table and change 50,000 records, that still
is only 1 update. Keep that in mind when reading the numbers provided.
*/

--This Version will only pull a list of tables that have shown no use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name

-- This version provides a list of all tables that are in use.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
usage.user_lookups,usage.user_scans, usage.user_seeks, usage.user_updates, usage.system_lookups,
usage.system_scans, usage.system_seeks, usage.system_updates, usage.last_user_lookup, usage.last_user_scan,
usage.last_user_update, usage.last_system_scan, usage.last_system_seek, usage.last_system_update
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
order by obj.name

--This version shows a sum of all activity on these tables.
select sch.name as SchemaName, obj.name as TableName, idx.name as IndexName, obj.object_id,
sum(usage.user_lookups) UserLookups,sum(usage.user_scans) UserScans, sum(usage.user_seeks) UserSeeks,
sum(usage.user_updates) UserUpdates, sum(usage.system_lookups) SystemLookups,
sum (usage.system_scans) SystemScans, sum(usage.system_seeks) SystemSeeks, sum(usage.system_updates) SystemUpdates
from sys.indexes idx
full outer join sys.dm_db_index_usage_stats as usage on idx.object_id = usage.object_id
and idx.index_id = usage.index_id
inner join sys.objects as obj on idx.object_id = obj.object_id
inner join sys.schemas as sch on sch.schema_id = obj.schema_id
where usage.database_id is not null
and sch.schema_id <> 4
and obj.object_id is not null
group by sch.name, obj.name, idx.name, obj.object_id
order by obj.name