Thursday, March 29, 2012

Database ID

I am new to DMVs and DMFs. If I run
select * from sys.dm_db_index_usage_stats
How do I know which database relates to the database_Id? likewise the
Object_Id and Index_ID? Is there a lookup table somewhere I could join on?
thanks
Adam,
you could use something like this:
use yourdatabasename -- (the database you are interested in examining)
go
select db_name(ius.database_id) as DatabaseName,
object_name(ius.object_id) as ObjectName,
sysind.name as IndexName
from sys.dm_db_index_usage_stats ius
inner join sys.indexes sysind on ius.index_id = sysind.index_id
For the moment it'll work fine, but you must be in the correct database for
the object_name function to return a valid name, hence the USE statement.
After SP2 has been released you'll be able to incorporate the database into
the object_name function to ensure you don't nee a use statement at the top.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

No comments:

Post a Comment