Using Profiler to capture database name and object

Use the database ID not the name but the Object Id is not that simple. What
object do you expect it to show? What if you are joining 10 tables? The
way to tell what is being accessed is to use the Index related DMV's such as
sys.dm_db_index_usage_stats & ys.dm_db_index_operational_stats. Here are a
few examples:

DECLARE @dbid INT
SELECT @dbid = DB_ID()

SELECT OBJECT_NAME(s.object_id) AS [objectname], s.object_id, i.name AS
[indexname], i.index_id
, user_seeks, user_scans, user_lookups, user_updates
FROM sys.dm_db_index_usage_stats AS s INNER JOIN sys.indexes AS i
ON i.object_id = s.object_id AND i.index_id = s.index_id
WHERE database_id = @dbid AND OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
ORDER BY (user_seeks + user_scans + user_lookups + user_updates) ASC

DECLARE @dbid INT

SELECT @dbid = DB_ID()

SELECT objectname=OBJECT_NAME(s.object_id), indexname=i.name, i.index_id
, reads=range_scan_count + singleton_lookup_count
, 'leaf_writes'=leaf_insert_count+leaf_update_count+
leaf_delete_count
, 'leaf_page_splits' = leaf_allocation_count
, 'nonleaf_writes'=nonleaf_insert_count + nonleaf_update_count +
nonleaf_delete_count
, 'nonleaf_page_splits' = nonleaf_allocation_count
FROM sys.dm_db_index_operational_stats (@dbid,NULL,NULL,NULL) s,
sys.indexes i
WHERE OBJECTPROPERTY(s.object_id,'IsUserTable') = 1
AND i.object_id = s.object_id
AND i.index_id = s.index_id
ORDER BY reads DESC

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Snake" wrote in message

>I need to setup profiler to capture which databases are being used and
>which
> tables in those databases are being accessed. For the life of me I can not
> get profiler to cough-up this info! It should be easy!!!! But
> Databasename
> and object name are always blank.
>
> Any suggestions?
>
>