Monday, November 04 2013
Keywords: SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name
SQL Profiler filtered by specific object (table, view, stored proc, function) name
"How could I filter the results of SQL Profiler filtered by a table name? I don't want to see all the other rows. Should I export to Excel for this?". So with that in mind, I created a DatabaseA, and in it I put 2 tables: tblFOO and tblClients. We are interested to filter the Profiler results by tblFoo. Let's go ahead and launch SQL Profiler. Create a New Trace. Use the "Standard (Default)" template for the purposes of this exercise. Go ahead and click on the "Events Selection" tab on top and then click on "Column Filters" button.
Scroll down and find TextData and set the Like clause. This is a standard SQL LIKE clause...if you only place tblFOO then the entire TextData value must exactly match 'tblFOO', which will never be the case. So you'd want to use % sign to do wild searches. Also Exclude rows where TextData is NULL, otherwise SQL Profiler will show you a union of %tblFOO% and NULL values.
If you get prompted that not all events include TextData, then click on YES.
I'd recommend that you would also filter by DatabaseName, so that the number of rows displayed in SQL Profiler results would radically decrease. however note that DatabaseName column is not a standard column output and thus you need to select it by checking it off.
Start the trace. In SSMS (SQL Server Management Studio) open a new query page and connect to DatabaseA. Test the work, by querying from tblFOO , and then from tblClients. Only results pertaining to tblFOO will show up.
We love this filtering schema...but we don't want to redo the whole configuration every single time we launch SQL Profiler. So what can we do to help us accelerate this procedure next time around? We would go ahead and stop the existing trace, and then Save As Trace Template.
Give it a name that pin points what it exactly does.
close the previous trace. Open a new trace, and this time instead of the Standard template, go ahead and scroll up and down until you find the template you just created.
SQL Profiler keeps all the filter settings of the template, however note that DatabaseName column in this example is not a standard column output and thus you need to select it again.
Written by Montreal DBA Team