Category: SQLCompass

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:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

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:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

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:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

…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.

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

But we can further narrow the search to exclude text contained in comments and literal text:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Now we’ve finally reduced our view to show only the one stored procedure which uses a database transaction – HumanResources . uspUpdateEmployeeHireInfo

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Click on this object and you can see where database transactions are used:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

SQL filtering offers a richer set of options than Schema and Name filtering; lets go through them now:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Advanced Filter

• Provides complex, object based filtering options which are covered in another article

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Contains

• SQL must contain the entered text (this is the default mode)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Like

• Asterix (*) matches any character
• Pipe (|) may be used to separate alternate values

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Regex

• Values must match a user defined Regex
• To learn more about Regexes click here

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Whole Word, Case Sensitive

• Applies whole word and case sensitivity constraints to the match

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Invert Match

• Inverts the match filtering OUT objects which match the search criteria

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Search All SQL

• Searches in all parts of the SQL definition

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Search Preamble Comment

• Searches only in the comment preceding the SQL definition (can be useful when searching for objects authored by a specific person)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Search Body

• Searches only in SQL body, excluding the comment preceding the SQL definition

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Ignore Comments, Ignore Literal Text

• Ignores the indicated portions of the SQL definition in the search

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

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)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

No Highlight

• Switches off match highlighting – this will marginally improve performance

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Clear SQL Filter, Clear Advanced Filter, Clear All

• Clears the SQL filter, the advanced filter or both

[vc_separator type=’normalt’ 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’]

Once you have created your snapshot you’re ready to start harnessing the power of SQLCompass’s filtering and navigation options

You’ll start off with an overview of the database with all objects visible in the tree view

Right click on the root (database) node and select Expand All to expand the sub nodes and see the actual objects:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

… and you should see something like this:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

SCHEMA AND NAME FILTERING

Start by filtering the objects by schema…

Type the text “p*|sales” into the Schema filter dropdown and press Enter

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

…you’ll see that objects are now restricted to those in a schema begining with ‘p’ or in the ‘sales’ schema

By default the schema filter uses ‘Like’ syntax where asterisk (*) will match any sequence of zero or more characters and pipe (|) may be used to separate alternate values

The different syntaxes which may be applied at Schema, Name or SQL level are:

Like

• Asterix (*) matches any character
• Pipe (|) may be used to separate alternate values

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Equals

• Values must exactly match the entered text (not relevant to SQL)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Starts With

• Values must start with entered text (not relevant to SQL)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Contains

• Values must contain the entered text

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Regex

• Values must match a user defined Regex
• To learn more about Regexes click here

Additionally, filtering may be modified by applying case sensitivity and whole word options to Schema, Name and SQL filters

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

And finally, the Invert Match option is very useful for finding objects which do not match a specific pattern

So, moving on to an example of filtering on both Schema and Name, type “prod*|*address*” into the Name filter and you should now see all objects in the “Sales” schema or schemas starting with “p” where the name of the object starts with “prod” or contains “address”

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

If you then invert the name filter you should now see all objects in the “Sales” schema or schemas starting with “p” where the name of the object does not start with “prod” and does not contain “address”

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

As you select objects in the tree view on the left you’ll see the SQL definition displayed in the Detail tab.

FILTERING ON SQL

Filtering on SQL is a larger topic and will be covered in another article

HISTORY

A history is maintained and updated as you visit each object allowing you to quickly navigate backwards  and forwards  through the objects in which you are interested

By right clicking on either the backwards or forwards button you can access a list of the most recently visited objects

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

This allows you to quickly move between the SQL you need to see to do your job

FILTER TOGGLE

If you need to quickly turnoff all filtering for a moment you can do this using the filter toggle

Click the button once to remove all filtering and then again to reactivate

GOTO

If you already know the object you’re looking for you can use the Goto menu  to quickly navigate to that object

First off use the filter toggle to remove all filters

Then press CTRL+G or click  to open the Goto menu and type “humanres.empl”…

You’ll see the menu now displays all objects where the Schema and Name name roughly match the entered text

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Then you can use the arrow keys or mouse to navigate to the desired object.

[vc_separator type=’normalt’ 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’]

A database snapshot can be created in one of two ways:

• Through a database connection
• From a script created from a database

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

To create a snapshot from a database script follow these steps:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
1) First create a CREATE script for all objects in the database (this can be done though SSMS by right clicking on the database and selecting Tasks > Generate Scripts..)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
2) From the quick access menu click New Document
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
3) Ensure the second tab (Snapshot From Script) is selected
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
4) In the Script dropdown enter the path to the script file or browse to this file if you prefer
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
5) Leave the Encoding value blank to use a default encoding which usually works
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
6) In the Server dropdown enter a descriptive the name for SQL Server instance or select from a history list (It is a good idea to use the actual name of the server where the script was generated)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
7) In the Suffix dowpdown (to the right of the Server dropdown) enter ‘-FromScript’. The suffix will be appended after the server name in the suggested filename and is optional
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
8) In the Database dropdown enter a descriptive database name or select from a historic list (again it is a good idea to use the actual name of the database which has been scripted)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
9) Check Any Database and Infer Schemas
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
10) Leave other checkboxes (Auto USE Database, Attach Script Actions, Include Unsupported Actions, Rewrite SQL) unchecked
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
11) Select a suggested filename for the snapshot from the Save As dropdown (or type a name if you prefer)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
12) Leave Full Build, One Click Refresh, Recursive Build and Offline unchecked
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
13) Select a filename suggestion from the Save As dropdown (or type a filename if you prefer) and Click OK (You may be prompted for a folder location if none has been specified)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
The snapshot will be created by analysing the SQL contained in the script, creating, altering and dropping objects in the virtual schema which is then saved in a single file.
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
This file can then be shared with other users of SQLCompass, allowing them to inspect and analyse the database schema with no requirement to be connected to the database or even a network.
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
So, let’s take a closer look at those options:
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Script(s)

• Enter the path to a file or files containing the script(s) to be processed to create the snapshot. ‘#’ may be used as a wildcard to match all files in a folder (similar to ‘*’ in general use) and ‘##’ may be used to match all files in a folder or subfolder.

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Encoding

• Specifies the encoding which was used when generating the script . If left blank then a default encoding of Windows-1252 (CP-1252) will be used which generally works in most cases. You may need to specify a different encoding if characters are displayed incorrectly in your snapshot

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Server

• Should contain a descriptive name of the server instance from which the script originated
•  Its a good idea to enter the named used to access the server instance here, but you can use any name to describe the server, e.g. PRODUCTION, QA etc.

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Database

• Should contain a descriptive name of the database from which the script originated
• Again its a good idea to enter the exact name of the database, but you can use any name
• Also this name can be relevant when the Any Database checkbox is unchecked

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Any Database

• When checked, all script fragments will be parsed and will contribute to the snapshot
• When unchecked, script fragments will only be processed where preceded by a USE statement which matches the database name specified
• This allows scripts referencing multiple database to be properly processed into multiple snapshots if required

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Auto USE Database

• When checked, all script fragments will be parsed and will contribute to the snapshot unless preceded by a USE statement which references a different database to that specified for the snapshot
• Use of this option is discouraged and it may well be removed in the future

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Attach Script Actions

• When checked, a collection of all parsed script actions contributing to the snapshot is retained as part of the snapshot
• As a result, all the actions leading to an object’s state can be listed against that object (e.g. a table which has been created through a CREATE TABLE fragment followed by several ALTER TABLE fragments in the processed script(s) which will detail the exact fragments alongside the final table structure)
• This can be very useful for debugging SQL error causes
• Generally it is recommended that this option be unchecked except where specifically required as it greatly increases the size of the snapshot

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Include Unsupported Actions

• Many script fragments are unrecognised by SQLCompass (e.g. SET ANSI NULLS…) and these unsupported actions will be ignored during parsing
• Checking this option will include these unsupported fragments in the attached script actions
• Generally it is recommended that this option be unchecked except where specifically required as it greatly increases the size of the snapshot

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Infer Schemas

• When checked, schemas will not be directly analysed but will be inferred from the schemas of analysed objects (as a result, empty schemas containing no objects will be omitted)
• When unchecked, schemas will be explicitly analysed from the database connection (this is generally discouraged and may lead to unexpected schemas being listed)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Rewrite SQL

• When checked, causes database object definitions to be rewritten in a standardised form
• This is a useful option to enable when comparing database snapshots, as trivial differences in the definition SQL can lead to undesired false positives (e.g. a datatype of [int] compared to INT will show as a difference when syntactically they are equivalent)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Full Build, On Click Refresh, Recursive Build, Offline

• These options are related to rebuilding of a snapshot rather than initial creation and will be covered in other articles

[vc_separator type=’normalt’ 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’]

A database snapshot can be created in one of two ways:

• Through a database connection
• From a script created from a database

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

To create a snapshot from a connected database use the following steps:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
1) From the quick access menu click New Document
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
2) Ensure the first tab (Snapshot From Connection) is selected
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
3) In the Server dropdown enter the name of SQL Server instance to connect to
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
4) In the Suffix dowpdown (to the right of the Server dropdown) enter ‘-FromConnection’. The suffix will be appended after the server name in the suggested filename and is optional
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
5) Choose from Integrated Security (Windows login) or enter a Username and Password (The app.config option may also be selected but this is an advanced option and will be covered in other articles)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
6) Select a database name from the Database dropdown (or type in the name if you prefer)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
7) Leave Incremental Build, Shallow Incremental Build and Infer Schemas checked
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
8) Select a suggested filename for the snapshot from the Save As dropdown (or type a name if you prefer)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
9) Leave Full Build, One Click Refresh, Recursive Build and Offline unchecked
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
10) Select a filename suggestion from the Save As dropdown (or type a filename if you prefer) and Click OK (You may be prompted for a folder location if none has been specified)
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
The snapshot will be created by analysing each database object through a live connection to the database and saved in a single file.
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]
This file can then be shared with other users of SQLCompass, allowing them to inspect and analyse the database schema with no requirement to be connected to the database or even a network.
[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

So, let’s take a closer look at those options:

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Server

• Should contain the name of the SQL Server instance to connect to
• This must exactly match the named instance to allow correct connection to the server

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Integrated Security

• When checked uses the current Windows user to connect to the server
• When unchecked a SQL Server username and password must be specified

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Use app.config

• Checking this option allows connection details to be accessed from an app.config file
• Use of this option will be discussed in other articles

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Username, Password, Database

• SQL Server username, password and database name to make the connection

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Incremental Build

• When checked, enables fast rebuilding of the snapshot where only changed objects are re-analysed
• When unchecked, all database objects will be reanalysed on rebuild

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Shallow Incremental Build

• When checked, Indexes and Foreign Keys will be reanalysed on rebuild only if the associated table object has changed
• When unchecked, all Indexes and Foreign Keys will be reanalysed on rebuild even when Incremental Build is checked.

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Infer Schemas

• When checked, schemas will not be directly analysed but will be inferred from the schemas of analysed objects (as a result, empty schemas containing no objects will be omitted)
• When unchecked, schemas will be explicitly analysed from the database connection (this is generally discouraged and may lead to unexpected schemas being listed)

[vc_separator type=’transparent’ position=’center’ color=” thickness=’0′ up=’25’ down=’25’]

Full Build, On Click Refresh, Recursive Build, Offline

• These options are related to rebuilding of a snapshot rather than initial creation and will be covered in other articles

[vc_separator type=’normalt’ 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’]