Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQL Profiler – Wild Card Filter on TextData

WARNING: This may be very obvious to most of you. However, several people that I have encountered did not realize this could be done. Therefore, I decided to share this.

For a DBA the SQL Server Profiler is a must have tool in his/her arsenal of items when trying to solve problems. Recently, I gave a presentation on the Profiler at SQL Saturday in Baton Rouge, LA. The topic was “An Introduction to the SQL Server Profiler”. There were approximately 40 attendees and of those in attendance about 95% were developers. There were a few DBAs in the audience.

I started the talk by discussing  concepts pertaining to profiler terminology, trace events, trace categories, etc… Then I moved on to filtering traces. I discussed a couple of the obvious trace filters such as: SPID, database, and duration. Then as expected one of the attendees asked, “Can you filter a trace by text within a query”. Not exactly like that, but once I finished my probing that is what it summed up to be. One of the DBAs in the audience, to my surprise, immediately said, “You can’t do that”. Hmmmm, I thought.

In my experiences as a DBA, especially at my current place of employment, I have become very proficient in using the profiler. The primary Operational Database that I am responsible for uses mostly Dynamic SQL, which makes my life difficult (sometimes). Therefore, I cannot use the Stored Procedure events and filter on the Object Name or the ObjectID.

I digress. I said to the DBA of little faith, “sure you can”. I opened the Profiler and created a trace that only contained the SQL:BatchCompleted event from the TSQL trace category. I only chose the TextData column from the list of available columns, and of course the SPID must be displayed.

clip_image002

Then I clicked the Column Filters Button and added %select *% and %select*% to the TextData filter under the LIKE choice. The attendee wanted to find all the queries that included a select *. Notice that I added two variations of the request. I did this because it is syntactically correct to issue either statement, and I wanted to ensure that all variations of the request were captured by the trace. Like this:

clip_image004

Notice that I added the %, which is a wild card character for T-SQL, to the beginning and end of each filter. As expected, it behaved just as it does when used in a T-SQL query. After running a couple of queries that contained both filters the results where:

clip_image006

As you can see the trace captured both of the statements. Surprisingly, not one person in attendance realized that the SQL Profiler was capable of performing such a filter. As always please provide your comments, thoughts, criticisms and ideas regarding this.

Talk to you soon

Patrick LeBlanc, Founder TSQLScripts.com

SQLDownSouth

Comments

Posted by David O on 21 August 2009

Great post.  I think pretty highly of my profiler skills and this was a very useful tip.  Sometimes the obvious isn't so obvious.

Posted by pparlapalli on 27 October 2010

Nice tip..Thanks

Posted by wbeetge on 23 February 2011

The problem is simply wrong choice of wods in the label

"Like" This already implies that the text wil be hanleded in the like manner, but it should have been "=" in order to tell developers that the wildcard syntax is needed.

Posted by cppprogrammer on 25 February 2013

Is it possible to have multiple conditions that are AND'd rather than OR'd? E.g. LIKE %select *% AND %dbo.Table%?

Posted by cppprogrammer on 25 February 2013

I'm just chaining the conditions atm but that implies I need one case per possible ordering of my conditions. E.g. 2 lines...

%cond1%cond2%

%cond2%cond1%

Posted by DavidBridgeTechnology.com on 2 April 2014

I came to this post on account of the same question posted by cppprogrammer on 25 February 2013

How to AND to search terms together rather than OR them.

It appears you have to combine the ANDed elements in to a single trace line such and separate them with a wildcard but order is important this way. This method is treating the search as if the like term is just a query on a complete string. Think of it a bit like a regular expression though with sql syntax (e.g. like patindex)

e.g.

"%Firstsearchterm%secondsearchterm%"

I'd love to know a better way if anyone has one.

Dave

Leave a Comment

Please register or log in to leave a comment.