Database.ca SQL Server Blog

My Blog

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.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

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.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

If you get prompted that not all events include TextData, then click on YES.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

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.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

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.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

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.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

Give it a name that pin points what it exactly does.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

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.

 

SQLProfilerFiltering\SQL Trace, SQL Server, Create SQL Trace Templates, SQL Profiler, Filtering by table, view, stored proc, function or object name

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 Ramin Haghighat