question

Sagar Bhargava avatar image
Sagar Bhargava asked

SQL Server profiler question

Hi, I am trying to gather information around who is accessing and what SQL is being executed against some specific tables in a database. I am expecting to see the table names under Objectname I only see it to be blank currently. Am I missing anything? Thanks.
profiler
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Which events are you capturing?
0 Likes 0 ·
I am capturing SQL:BatchCompleted, SQL:BatchStarting and RPC:Completed. I have now added SP:StmtStarting and SP:StmtCompleted as well. I have setup column filters on Database and mentioned multiple tables under the Objectname. Ran some TSQL against the tables that I would like to monitor and the Objectname field is still blank though the text data is being populated.
0 Likes 0 ·
Get you solve this problem?
0 Likes 0 ·
No, but I have captured text data so will load into a table and will filter and extract the results.
0 Likes 0 ·
Kev Riley avatar image
Kev Riley answered
ObjectName is only populated for some events. In the case of RPC:Completed, SP:StmtStarting and SP:StmtCompleted, it will be the **stored procedure** name. I'm not sure there any any events that relate to a single table - for any given T-SQL batch there could easily be many tables used. Maybe possible to capture locks - the column ObjectID2 will be the partitionid that you can use to lookup against sys.partitions to get an actual objectid - but this will capture a LOT of events!
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

shamim007 avatar image
shamim007 answered
i use some DMVs hope it would help you SELECT DB_NAME(ius.[database_id]) AS [Database], OBJECT_NAME(ius.[object_id]) AS [TableName], MAX(ius.[last_user_lookup]) AS [last_user_lookup], MAX(ius.[last_user_scan]) AS [last_user_scan], MAX(ius.[last_user_seek]) AS [last_user_seek] FROM sys.dm_db_index_usage_stats AS ius WHERE ius.[database_id] = DB_ID() AND ius.[object_id] = OBJECT_ID('YourTableName') GROUP BY ius.[database_id], ius.[object_id];
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.