#0127 – SQL Server – SSMS – Tutorial – Part 02 – Introduction to the Object Explorer and Object Explorer Details window (L100)


As you know, we are exploring the world of graphical tools & utilities that are available to us out of the box in a Microsoft SQL Server installation. In the previous part of the tutorial, I provided an introduction to the SQL Server Management Studio (SSMS) and the Query Editor window. I hope that you have started exploring these most important tools.

In this part, I will help you explore the two most important windows after the Query Editor. These are the Object Explorer & the Object Explorer Details. Both are related, and therefore, easier to understand.

The base functionality offered by these windows was around at least since the days of SQL Server 7.0. We lost most of this functionality when SQL Server underwent massive re-engineering in SQL Server 2005, but they were reintroduced starting SQL Server 2008.

Object Explorer window

The Object Explorer window is perhaps the 2nd most used part of the SQL Server Management studio. The Object explorer has the ability to connect to following services of SQL Server:

  1. Database Engine instances
  2. Analysis Services
  3. Integration Services
  4. Reporting Services
  5. SQL Server Compact editions

The primary functions of the Object Explorer are:

  • Provides a view of the objects available in the SQL Server
  • Provides a user interface, or invokes wizards to manage these objects

The Object Explorer UI

The object explorer is so powerful, that it comes with it’s own UI elements – a toolbar and a working area. Here’s the screenshot.

Object Explorer - Base View

To connect to the object explorer, click on the “connect” button in the Object Explorer toolbar, which pulls up the “Connect to Server…” dialog box. Once logged in, the user immediately notices that all database objects are neatly arranged in the logical groups. When connected to a database engine, the groups are:

  1. SQL Server instance – allows you to:
    1. Manage SQL Server configuration
    2. Manage part of the SQL Server surface configuration
  2. Databases – allows you to:
    • View a list of accessible databases, their states
    • View information about database objects
    • Create & modify database objects
    • Manage databases
    • Manage database security
  3. Security – allows you to:
    • Manage SQL Server instance security, including logins
  4. Server Objects – allows you to:
    • Manage endpoints
    • Manage devices
    • Manage linked servers
  5. Replication – allows you to:
    • Manage & configure replication
  6. Management
    • View activity log information
    • Run basic
  7. Integration Services
  8. SQL Server Agent
    • Setup, configure and manage agent jobs
    • Manage SQL Server automation

Not only does the object explorer allow an administrator to manage the SQL Server instance, it also allows a developer to:

  1. Filter & identify database objects from all objects of a database
  2. Once filtered, the developer can script these objects out

Filtering objects within the object explorer

We can summarize filtering within the object explorer in 3 simple steps!

Step 01:
A. In the Object explorer, right click on the “Tables”
B. Go to Filter –> Filter Settings
Filter objects view
Step 02:
Enter the object information as necessary. For our case, enter:
Name = Person
(If you only want to work within a schema, enter only the schema name)
Filter Settings
Step 03:
Simply click “OK” to apply the filters in the Object Explorer
Filtered objects in the Object Explorer
Step 04:
To remove filtering, all one needs to do is:
A. In the Object explorer, right click on the “Tables”
B. Go to Filter –> Remove Filter
Removing filters

Filtering is applicable to all object types within the Object Explorer. If one is to only work on a particular schema or a set of objects, it is very productive to have only those required objects filtered out. For SQL Server 2005 users, there are no bad news because this feature is available in Microsoft SQL Server 2005 as well.

Scripting a database object

Once you have filtered for the required object, you can script out the object for saving a copy or deploying the object on another database/SQL Server instance. Again, we have a very concise sequence of steps:

Step 01:
Right click on any object.
 
Step 02:
Go to Script Table As –> Create To –> New Query Editor window
Scripting objects from the Object Explorer
Step 03:
A script that can be used to recreate a database object is now generated.
Scripted object

Adjusting scripting options

You can adjust the scripting options to suit your standards and needs. For example, many prefer that the script check for pre-existing versions of the objects before going ahead with the deployment. To adjust the scripting options, you need to follow the following steps:

Step 01: Go to Tools –> Options Options window
Step 02: Under SQL Server Object Explorer, go to “Scripting” Scripting options
Step 03: Configure scripting options of your choice depending upon your project/organization best practices Scripting Options

Once these options are set, the SSMS will follow these settings for all scripting capabilities available.

Object Explorer Details window

In addition to providing object listing functionality same as the Object explorer, the Object Explorer details also provides certain additional information:

  1. Database information
    • Information about database owner, space available, recovery model and other database properties
  2. Table & index usage
    • Information about # of rows in a table/index, size of table/rows, etc
  3. Space utilization
    • Log and data file space utilization

The object explorer details is not launched by default. To launch the Object explorer details window, simply hit F7 after launching SSMS or go to View->Object explorer details.

IMPORTANT: On 32-bit operating systems, Object Explorer can only display 64,000 objects. An icon must be selected to access additional objects.

The following screenshots demonstrate how you can move about the object explorer details window via a drill-down mechanism.

Object explorer base state image
Drill down to the “Databases” node, and select a database to view database properties
(Also, you can use the column chooser)
image
Double click on the database, browse out to the “Tables” node and double-click on any table to get the table Details image
Index Details image

Object Search

One of the best features I like is the object search. It’s power-packed, fast and is absolutely geared towards reducing developer and administrator efforts.

Here are the brief steps:

  1. Navigate to the Object Explorer Details window by using the View menu or pressing the (F7) key when in the SQL Server Management Studio
  2. Drill down to the database against which the search needs to be carried out
  3. In the Search bar, enter the object name that you would like to search for (you can use wild-card characters)
  4. Simply hit “Enter” to execute the search!

Object Explorer Details search

If no specific database is chosen, the search is executed across all databases in the entire SQL Server instance. This is very useful feature, however, can be very slow. If you happen to use this accidentally, there’s always the panic button labeled – “Stop”!

Progress bar on the object explorer window

Once an object is found, one might want to look for it in the Object Explorer. To do so, simply right-click and choose “Synchronize”.

Step 01: Right-click and choose “Synchronize” Object Synchronization
Step 02: Notice that both the object explorer and object explorer details now points to the selected object Object Synchronization

In addition, the object explorer details window allows the administrator or the developer to script out the object.

Practice Exercises/How To…?

It’s time to practice! The practice exercises for Object Explorer and the Object Explorer details are fast, simple and easy.

Object Explorer

  1. Explore how to start and stop the SQL Server and the SQL Server Agent services via the Object Explorer window
  2. From the AdventureWorks2008R2 database, attempt to script at least 2 similar objects (stored procedures, triggers, etc) to a single script together

Object Explorer Details

  1. Create the following stored procedure within the AdventureWorks2008R2 database
    CREATE PROCEDURE proc_tmpOESearch
    AS
    BEGIN
        PRINT 'BeyondRelational.com'
    END
    GO
      
    • Search for the text – “BeyondRelational” within the AdventureWorks2008R2 database. Are any results returned?
    • Now, search on the object name – “OESearch” within the AdventureWorks2008R2 database. Are any results returned?
  2. Search for the object names using wild cards  – “dm_os%counters” within the AdventureWorks2008R2 database
  3. Can you search for objects using the .NET regular expressions?
  4. From the AdventureWorks2008R2 database, attempt to script at least 2 similar objects (stored procedures, triggers, etc) to a single script together. Are the results different from the Object Explorer window?

Further Reading

Until we meet next time,

Be courteous. Drive responsibly.

Subscribe to my posts via E-mail: Subscribe here | Read my posts via your favourite RSS reader: Click Here!

Advertisements

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s