Unsed Tables in Databse

In SQL Server 2005 you can use the sys.dm_db_index_usage_stats data
management view
SELECT
last_user_update = MAX(last_user_update)
FROM
sys.dm_db_index_usage_stats
WHERE
[database_id] = DB_ID()




"NAVIN.D" wrote in message

> DMV to identify the unsed tables and most used tables in database?
 >> Stay informed about: Unsed Tables in Databse 

The following query returns each user table contained in a database and uses
two different methods to determine if it is "used". First, it determines if
the table name is contained within the text of any other object (stored proc,
view, trigger, or function) within the database. Also, it joins
[sys.dm_db_index_usage_stats] to determine if there are usage statisics for
that table. On a large systems with hundreds of tables and procedures, it may
take 1/2 an hour or more to complete.

Keep in mind that usage statistics are reset whenever the server is
rebooted, so you will need to fully regression test your application,
insuring that every module and function call is performed, prior to running
this query.

To be really thorough, when you have identified the those tables you suspect
are not used, regression test your application again with a SQL Profiler
trace running and filtering events on those specific objects.

select
(case when ObjectName is null and TotalUserAccess = 0 and
TotalSystemAccess = 0 then 'X' else '' end) as NotUsed,
*
from
(
select
TBL.name as TableName,
OBJ.name as ObjectName,
OBJ.type as ObjectType,
sum(isnull(user_seeks,0) + isnull(user_scans,0) +
isnull(user_lookups,0) + isnull(user_updates,0)) as TotalUserAccess,
sum(isnull(system_seeks,0) + isnull(system_scans,0) +
isnull(system_lookups,0) + isnull(system_updates,0)) as TotalSystemAccess
from sys.objects as TBL
-- Outer join, so it also lists non-referenced tables.
left join sys.syscomments as OBJTEXT
on OBJTEXT.text like '[^a-z,0-9,_]%' + TBL.name + '%[^a-z,0-9,_]'
left join sys.objects as OBJ
on OBJ.object_id = OBJTEXT.id and
OBJ.is_ms_shipped = 0 -- Exclude system objects
left join sys.dm_db_index_usage_stats as USAGESTATS
on USAGESTATS.object_id = TBL.object_id
where
TBL.type = 'U' and -- User Tables
TBL.is_ms_shipped = 0 -- Exclude system objects
group by
TBL.name,
OBJ.name,
OBJ.type
) as X
order by
(case when ObjectName is null and TotalUserAccess = 0 and
TotalSystemAccess = 0 then 'X' else '' end) desc,
TableName,
ObjectName


"NAVIN.D" wrote:

> DMV to identify the unsed tables and most used tables in database?
 >> Stay informed about: Unsed Tables in Databse