ADVANCED FILTERING
Advanced filters extend the basic SQL filtering functionality by exposing object based predefined filters to search for specific patterns
For example, advanced filters are available to search for INSERT, UPDATE, DELETE and SELECT patterns on a specific table or for EXEC patterns on a specific stored procedure
These filters can be combined to search (for example) for any code which updates a specific table, or calls a specific stored procedure
The following steps show how this is done:
1) Open up a snapshot of the AdventureWorks database, expand ‘Tables’ in the tree view and right click on the dbo.ErrorLog table:
2) Click on ‘Advanced Filter…’ and the Advanced Filter window will open with the dbo.ErrorLog table preselected as the active object
(you can change the active object in this window by pressing Ctrl+G to access the local goto menu)
3) Place a check mark against the INSERT and UPDATE clauses in the lower pane and you will see these copied and checked in the upper pane
4) Click ‘Apply’
You will see that the tree view changes, removing all objects except the store procedure dbo.uspLogError because this is the only object which updates or inserts into dbo.ErrorLog
5) Right click on this stored procedure (dbo.uspLogError) and again click on ‘Advanced Filter…’
You’ll notice the active object in the Advanced Filter has now changed to the stored procedure
6) Place a check mark against the EXEC clause in the lower pane and you will see this copied and checked in the upper pane
7) Click on ‘Apply’ to apply the new filter which will bring all objects into scope which either insert into dbo.ErrorLog, update dbo.ErrorLog or execute dbo.uspLogError
Now you’ll see that 3 more stored procedures have come into scope
You can add as many clauses as you need into the filter and temporarily disable them by removing the check mark, or you can delete them from the upper pane by clicking on the 
symbol to the left of a clause
Advanced filtering builds calculated regexes to match patterns. These are displayed to the right of each clause and can be a useful resource when you build your own regexes
SELECTIVE FILTER
By default the Advanced Filter is applied to all objects which can sometimes produce undesirable results, for example if you need to find objects which affect a number of different tables you’ll see that all the tables will disappear from the tree view once the filter is applied
The Selective Filter is accessed through a second tab in the Advanced Filter window and allows you to override each of the different filters based on object type
You can quickly bring all tables back into scope by checking ‘Show All’ in the ‘Tables’ row of the Selective Filter or alternatively uncheck ‘Advanced’ in the ‘Tables’ row (in which case you can still use other filters to reduce the tables shown in the tree view)
You can also hide all objects of a particular type (which can be useful when generating scripts or documentation) or switch off ‘Schema’, ‘Name’, ‘SQL’, ‘Advanced’ and ‘Compare’ filters
The ‘Related’ filter only applies to indexes and foreign keys and restricts scope for these objects to those relating to table in scope
[vc_separator type=’normal’ position=’left’ color=’#03275A’ thickness=’1′ up=’10’ down=’10’]
Just started working with or interested in learning about how SQL works? Click here.
[vc_separator type=’normal’ position=’left’ color=’#03275A’ thickness=’1′ up=’10’ down=’10’]