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.
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.
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!
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];
19 People are following this question.
