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?

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote

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" <Snake (AT) discussions (DOT) microsoft.com> wrote