DBA xPress Schema Object Browser

Overview

IconDescription
DBA xPress Object Browser iconObject Browser enables you to view details about the objects in a database schema.

Feature Highlights
  • Easily view details about objects in a database
  • Generate DDL scripts

Opening the Object Browser

Launch the DBA xPress Object Browser by completing the following steps:

  1. Open the SentryOne workbench, then select Object Browser to open the Connect to Database window.DBA xPress select Object Browser
  2. Enter a SQL Server connection or select a previous connection from the drop down list. Enter your connection credentials, then select a database for the comparison. Select OK to load the Object Browser for your selection.
DBA xPress Connect to Database select SQL Server
DBA xPress Connect to Database Enter credentials
DBA xPress Connect to Database select Ok

Note:  After entering a database connection and selecting OK, the Object Browser loading screen displays. The loading time varies based on the size of the database connection. DBA xPress Object Browser Loading content

Success: You are now ready to navigate the Object BrowserDBA xPress Object Browser

Navigating the Object Browser

With the DBA xPress Object Browser, you can view the list of schema elements for a database in a tree structure. The following schema elements are viewable:

  • Tables
  • XML Schema Collections
  • File groups
  • Service Message Types
  • Views
  • Rules
  • Partition Functions
  • Service Contracts
  • Synonyms
  • Defaults
  • Partition Schemes
  • Service Queues
  • Programmability
  • Routes
  • Security
  • Services
  • Procedures
  • Remote Service Bindings
  • Users
  • Database Roles
  • Functions
  • Broker Priorities
  • Roles
  • Schemas
  • DDL Triggers
  • Storage
  • Application Roles
  • Asymmetric Keys
  • Assemblies
  • Full Text Catalogs
  • Plan Guides
  • Certificates
  • Data Types
  • Full Text Stop Lists
  • Service Broker Objects
  • Credentials

Select the next to any category name to display the schema elements for that category.

DBA xPress Object Browser Expand element
DBA xPress Object Browser Element expanded

Selecting a schema element displays its properties on the right side of the window, and the script used to generate the object.DBA xPress Object Browser selected Schema

Type into the search box at the top of the Object Browser to initiate a search for your object. Search results display as a selectable list that can be interacted with in the same way as the tree view.DBA xPress Object Browser Search bar

Note:  Select the to cancel a search. Make changes to the search parameters by right-clicking the magnifying glass. DBA xPress Object Browser Search parameters

The following search match types are selectable:

  • Qualified Name Is
  • Qualified Name Contains
  • Object Name Is
  • Object Name Contains
  • Schema Name Is
  • Schema Name Contains

For information about more complex searches, see the Filter Trees tab.

Filter Tree Overview

The underlying engine that powers the Object Browser allows you to use complex filters to select objects based on multiple criteria. Filters are based on a tree system and are split into two categories:

Filter TypeDescription
Combination elements (Compound filters)Compound filters combine other elements to produce a result. For example, if all sub-elements match elements, then the filter produces a positive result.
Match ElementsMatch elements produce a result based on inspection of a schema element. For example, is the name of the schema that this object belongs to Sales.

Compound Filter TypesDBA xPress Choose Filter window Compound

TypeDescription
AndThe compound filter returns a positive match if all child elements return a positive match.
OrThe compound filter returns a positive match if any of the child elements return a positive match.
Exclusive OrThe compound filter returns a positive match if one and only one of the child elements returns a positive match.

Match ElementsDBA xPress Choose Filter window Match Elements

TypeDescription
Object Type Filter
The filter returns a positive match if the object matches the filter type in the specified manner.
Object Type Group Filter
The filter returns a positive match if the object matches the filter type group in the specified manner.
Text Filter
The filter returns a positive match if the selected text property of the object matches the filter text in the specified manner.

Filter ModeDBA xPress Choose Filter window Mode

At the top of the filter dialog is a drop down list marked Mode. This is used to determine whether the filter tree includes only objects that match the filter, or excludes objects that match the filter.

Filter Root Properties

DBA xPress Choose Filter window Filter Root

Each filter has a root filter element which is the element that brings all other filter elements together. This element is a special type of compound filter, with two extra properties:

PropertyDescription
NameThe name of the filter refers to the filter in dialogs and the recently used filter list.
Inheritance

The inheritance mode specifies if an object can match based on if a related object also matches. For example, when matching a table, it might be useful to match all the columns belonging to that table as well. There are two filter modes that facilitate this:

  • From parent object only
  • From any referenced object

Inheritance Modes

ModeDescription
From parent object onlyLooks at the current object's parent, and if that matches, then the current object will match. If the parent does not match, then the parent's parent will be checked, until the chain is searched.
From any referenced objectLooks at all of the objects that the current object references, and if any of those match, then the current object will match.

Match Elements

Object Type Filter

The object type filter has two properties, Mode and Type

Note:  The Type is simply the type of object, such as  Table, Column, View, etc.

Mode

The Mode specifies how the object type match is applied. The following Mode types are available:

ModeDescription
isSpecifies that the object matches if its type is the same as the selected type.
is notSpecifies that the object matches if its type is not the same as the selected type.
is not and is not parented bySpecifies that the object matches if its type is not the same as the selected type, and the object is not parented by the selected type.
is not parented bySpecifies that the object matches if it is not parented by the selected type.
is or is parented bySpecifies that the object matches if its type is the same as the selected type, or it's parented by the selected type.
is parented bySpecifies that the object matches if it's parented by the selected type.

Parenting Rules

The following table displays parent object types their related child objects:

Parent Object TypeChild Object
Table
  • Column
  • Primary Key Constraint
  • Check Constraint
  • Unique Constraint
  • Default Constraint
  • Foreign Key Constraint
  • Relational Index
  • XML Index
  • Spatial Index
  • Statistics Collection
  • Assembly DML Trigger
  • DML Trigger
Default
  • Type Rule Binding
  • Column Rule Binding
Rule
  • Type Default Binding
  • Column Default Binding
View
  • View
  • Relational Index
  • XML Index
  • Spatial Index
  • Statistics Collection
  • Assembly DML Trigger
  • DML Trigger
Assembly
  • Assembly Stored Procedure
  • Aggregate Function
  • Assembly Scalar Function
  • Assembly Table Function
  • Assembly DML Trigger
  • Assembly DDL Trigger
  • Assembly Type
Partition Function
  • Partition Scheme
Full Text Catalog
  • Full Text Index
Full Text Index
  • Full Text Stop List
Service Queue
  • Service
  • Event Notification
Service Contract
  • Service
Column
  • Check Constraint
  • Default Constraint
File group
  • Table
  • Relational Index
  • XML Index
  • Spatial Index
  • Full Text Catalog
Schema
  • Table
  • Synonym
  • Default
  • Rule
  • Primary Key Constraint
  • Check Constraint
  • Unique Constraint
  • Default Constraint
  • Foreign Key Constraint
  • Assembly Stored Procedure
  • Aggregate Function
  • Assembly Scalar Function
  • Assembly Table Function
  • Assembly DML Trigger
  • Stored Procedure
  • Replication Filter Procedure
  • View
  • Scalar Function
  • Inline Table Function
  • Table Function
  • DML Trigger
  • Assembly Type
  • XML Schema Collection
  • User Defined Type

Object Type Group Filter

The object type group filter has two properties: Mode and Group.

Note:  Mode determines whether the filter matches an object because it does or does not belong to the specified group.

Group

Groups are logical groupings of object types. The following table lists the Groups and their associated object types:

GroupObject Types
All Assembly Objects
  • Assembly Stored Procedure
  • Aggregate Function
  • Assembly Scalar Function
  • Assembly Table Function
  • Assembly DML Trigger
  • Assembly DDL Trigger
  • Assembly
  • Assembly Type
All CLR Programmable Objects
  • Assembly Stored Procedure
  • Aggregate Function
  • Assembly Scalar Function
  • Assembly Table Function
  • Assembly DML Trigger
  • Assembly DDL Trigger
All DDL Triggers
  • Assembly DDL Trigger
  • DDL Trigger
All DML Triggers
  • Assembly DML Trigger
  • DML Trigger
All Functions
  • Scalar Function
  • Inline Table Function
  • Table Function
  • Aggregate Function
  • Assembly Scalar Function
  • Assembly Table Function
All Partitioning Objects
  • Partition Function
  • Partition Scheme
All Programmable Objects
  • Assembly Stored Procedure
  • Aggregate Function
  • Assembly Scalar Function
  • Assembly Table Function
  • Assembly DML Trigger
  • Assembly DDL Trigger
  • Stored Procedure
  • Replication Filter Procedure
  • View
  • Scalar Function
  • Inline Table Function
  • Table Function
  • DML Trigger
  • DDL Trigger
All Service Broker Objects
  • Event Notification
  • Service Message Type
  • Service
  • Service Contract
All SQL Functions
  • Scalar Function
  • Inline Table Function
  • Table Function
All SQL Programmable Objects
  • Stored Procedure
  • Replication Filter Procedure
  • View
  • Scalar Function
  • Inline Table Function
  • Table Function
  • DML Trigger
  • DDL Trigger
Constraints
  • Primary Key Constraint
  • Check Constraint
  • Unique Constraint
  • Default Constraint
  • Foreign Key Constraint
Data Storage Objects
  • Table
  • View
Full Text Indexing Objects
  • Full Text Catalog
  • Full Text Stoplist
  • Full Text Index

Indexes
  • Relational Index
  • XML Index
  • Spatial Index
Principals And Memberships
  • Application Role
  • Database Role
  • Database User
  • Role Member
Rule/Default Bindings
  • Type Default Binding
  • Type Rule Binding
  • Column Default Binding
  • Column Rule Binding
Table Child Objects
  • Column
  • Primary Key Constraint
  • Check Constraint
  • Unique Constraint
  • Default Constraint
  • Foreign Key Constraint
  • Relational Index
  • XML Index
  • Spatial Index
  • Statistics Collection
  • Assembly DML Trigger
  • DML Trigger

Text Filter

The text filter has four options: Source, Mode, Text, and Case sensitive match

Note:  Text is the text that you want to search for. Case sensitive match specifies whether the text searched for matches the case of the entered text.

Source

Source specifies where to look for the specified text.

SourceDescription
Object definitionSpecifies that the object's definition is the place where the text is searched for.

Note:  Definition applies to any object which is SQL programmable (i.e. views, procedures, functions, triggers).
Object nameSpecifies that the object's name is the place where the text is searched for.
Object name or parent object nameSpecifies that the object's name, as well as the parent object's name, are the places where the text is searched for.
Object or parent definitionSpecifies that the object's definition, as well as the parent object's definition, are the places where the text is searched for.

Note:  Definition applies to any object which is SQL programmable (i.e. views, procedures, functions, triggers).
Object owner nameSpecifies that the name of the object's owner (i.e. database principal) is the place where the text is searched for.

Note:  Under SQL Server 2000, the object's owner is the same as the object's schema.
Object schema nameSpecifies that the name of the object's schema is the place where the text is searched for.
Parent object definitionSpecifies that the parent object's definition is the place where the text is searched for.

Note:  Definition applies to any object which is SQL programmable (i.e. views, procedures, functions, triggers).
Parent object nameSpecifies that the parent object's name is the place where the text is searched for.

Mode

The Mode specifies how to look to match the specified text with the selected text from the object.

ModeDescription
begins withSpecifies that the source text must begin with the specified match text.
containsSpecifies that the source text must contain the specified match text.
does not begin withSpecifies that the source text must not begin with the specified match text.
does not containSpecifies that the source text must not contain the specified match text.
does not end withSpecifies that the source text must not end with the specified match text.
does not match regular expressionSpecifies that the source text must not match the regular expression that is specified by the match text. Note that the regular expression must conform to the standard Microsoft .NET regular expression format.
ends withSpecifies that the source text must end with the specified match text.
isSpecifies that the source text must be exactly the same as the specified match text.
is notSpecifies that the source text must not be exactly the same as the specified match text.
matches regular expressionSpecifies that the source text must match the regular expression that is specified by the match text.

Note:  The regular expression must conform to the standard Microsoft .NET regular expression format.

Creating a Filter Tree

Create a filter tree in your Object Browser by completing the following steps:

1. Select Add Filter Tree to open the Choose Filter window. DBA xPress Object Browser Add Filter Tree

Note:  Initially, the only element in the Choose Filter window is the filter root. The properties of the filter root are displayed at the bottom of the window.

2. Right click any element where you want to add a new child element, and then select the type of child element to add from the context menu. Repeat this step to add as many filter elements as you desire.DBA xPress Choose Filter window add child element

Note:  You can only add child elements to compound filters (And, Or, Exclusive Or, and the root filter).

3. Select OK to add your filter to the Object Browser.DBA xPress Choose Filter window select Ok

Choose Filter Buttons

DBA xPress Choose Filter window Ok buttonOpens the Enter Filter Name window.

Enter a filter name for your filter, then select OK to add your filter to the Object Browser.

DBA xPress Choose Filter window Cancel buttonCloses the Choose Filter window.
DBA xPress Choose Filter window New buttonClears the Choose Filter window and allows you to begin creating filters again.

Note:  If you have made any changes to your current filter, selecting New prompts you to save your changes before clearing the filter.
DBA xPress Enter File Name window
DBA xPress Choose Filter window Load from Palette buttonLoad a filter from the filter palette.

Select the desired folder and filter, then select OK to load your filter.
DBA xPress Browse Filter Palette window
DBA xPress Choose Filter window Save to Palette buttonOpens the Add to Filter Palette window.

Enter a name for your filter, select a folder for the filter, and then select OK to save your filter to the Palette.

Note:  You need a palette filter to save your filter. Select New to open the Enter Folder Name window. Enter a folder name, then select OK to save your palette folder.

Note:  If a filter has been saved to the palette, it is available for use for filtering in all SentryOne Workbench applications. You can also insert the contents of a filter in the palette as a sub-element of a filter by right clicking on the element to be the parent, selecting Add From Filter Palette, and then choosing the relevant filter. This way, a series of complex filters can be combined quickly into one filter.
DBA xPress Add to Filter Palette window
DBA xPress Choose Filter window Import Filter buttonLoad a previously saved filter(.xml) file.

Select Import Filter to open the Import Filter window. Select a filter, and then select Open to load your filter. 
DBA xPress Import Filter window
DBA xPress Choose Filter window Export Filter buttonSave your filter as an (.xml) file.

Select Export Filter to open the Export filter window. Enter a name for your filter, then select OK to save your filter.
DBA xPress Export Filter window
DBA xPress Choose Filter window Recently Used buttonDisplays a list of the recently used filters, allowing a filter to be re-used quickly and easily without the need to save it.DBA xPress Choose Filter window Recently Used

 Right clicking a filter tree gives you the option to remove it, or to edit the filter associated with it. DBA xPress Object Browser Filter Tree context menu

Note:  Filter trees are collapsed as much as possible. A filter tree which would only show stored procedures, simply shows the stored procedures under the main tree node, and the Programmability and Stored Procedures nodes are removed.

Example Filter

The following example finds objects that fall into on of the following categories:

  • Stored procedures in the Sales or Marketing schema that do not contain the text --temporary_object
  • Tables in the Sales schema
  • Views in the Marketing schema

DBA xPress Choose Filter window example filter

Note:  The root of the filter is in Or mode because there are three distinct conditions, and any of these conditions can produce a match. 

Under the root filter, there are three compound filters, one for each condition listed above:

Filter Element ImageDescription
DBA xPress Choose Filter window example part oneThe first filter component handles the criteria:

  • Stored procedures in the Sales or Marketing schema that do not contain the text --temporary_object

We have an object type filter set to match stored procedures. We also have a compound filter which will match if the object schema name is Sales or if the object schema name is Marketing. Then we have a text filter which will match if the object definition does not contain the text --temporary object.
 
DBA xPress Choose Filter window example part twoThe next compound filter deals with the criteria:

  • Tables in the Sales schema
We have an object type filter set to match tables, and we have a text filter which will match if the object schema name is Sales.
DBA xPress Choose Filter window example part threeThe last compound filter deals with the criteria:

  • Views in the Marketing schema
 We have an object type filter set to match views, and we have a text filter which will match if the object schema name is Marketing.