10 Jun Filtering on SQL
Filtering on Schema and Name is a great way to narrow things down if you know what you’re looking for, but if you’re working with an unfamiliar database or you can’t remember what you called that stored procedure (or was it a view?) then you need to start filtering on the SQL definition
One of the real benefits of working with an in-memory, offline model of the database is that SQL filtering is almost instantaneous for all but the most complex searches.
So, lets start by opening our AdventureWorks2014 snapshot which we created in previous articles.
If you haven’t already done this you can build yourself a snapshot by following the steps in this article.
Once you have your snapshot right click on the root node in the tree view and click Expand All.
You’ll see all the tables, view, stored procedures and other database objects in the tree view and a summary of these objects in the Details pane to the right.
So now to look for the word “transaction” in the SQL of all these objects simply type “transaction” into the SQL filter dropdown and press Enter.
You’ll now see the tree view shows only objects containing the word “transaction” in the SQL definition:
If you then click on the first object – Production . TransactionHistory you’ll see the definition of this table in the Detail pane and you’ll notice the word “transaction” has been highlighted
But lets say for the sake of this example that we’re looking for a database transaction and are not interested in the TransactionHistory table or the TransactionID column…
In which case lets modify the filter mode to match whole words only by selecting that option in the SQL filter menu:
Now we’ve reduced our filtered items to dbo . uspLogError and HumanResources . uspUpdateEmployeeHireInfo
By clicking on dbo . uspLogError we can see the definition of that object in the Detail pane with “transaction” highlighted 4 times:
…but we still haven’t actually located a database transaction; we’ve found the work “transaction” in a SQL comment and in some literal text.
But we can further narrow the search to exclude text contained in comments and literal text:
Now we’ve finally reduced our view to show only the one stored procedure which uses a database transaction – HumanResources . uspUpdateEmployeeHireInfo
Click on this object and you can see where database transactions are used:
SQL filtering offers a richer set of options than Schema and Name filtering; lets go through them now:
Advanced Filter
- Provides complex, object based filtering options which are covered in another article
Contains
- SQL must contain the entered text (this is the default mode)
Like
- Asterix (*) matches any character
- Pipe (|) may be used to separate alternate values
Regex
- Values must match a user defined Regex
- To learn more about Regexes click here
Whole Word, Case Sensitive
- Applies whole word and case sensitivity constraints to the match
Invert Match
- Inverts the match filtering OUT objects which match the search criteria
Search All SQL
- Searches in all parts of the SQL definition
Search Preamble Comment
- Searches only in the comment preceding the SQL definition (can be useful when searching for objects authored by a specific person)
Search Body
- Searches only in SQL body, excluding the comment preceding the SQL definition
Ignore Comments, Ignore Literal Text
- Ignores the indicated portions of the SQL definition in the search
Highlight Only
- Highlights matched portions of the SQL definition without applying the filter (so all objects will be in scope whether they match the filter or not)
No Highlight
- Switches off match highlighting – this will marginally improve performance
Clear SQL Filter, Clear Advanced Filter, Clear All
- Clears the SQL filter, the advanced filter or both