Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This tutorial will help you learning SQL Server Management Studio (SSMS)

Authors

Getting Started with SSMS

Getting Started with SSMS - Part 2: Introduction to the Object Explorer and Object Explorer Details Window

Sep 13 2012 12:00AM by Nakul Vachhrajani   

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

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:

    • Manage SQL Server configuration
    • 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 Setting

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)

object information

Step 03:

Simply click OK to apply the filters in the Object Explorer

applied filters

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

remove filter

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

New Query Editor Window

Step 03:

A script that can be used to recreate a database object is now generated.

script to recreate database

Adjusting scripting options

You can adjust the scripting options to suit your standards and needs. For example, many prefer that the script check for preexisting 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

Step 02:

Under SQL Server Object Explorer, go to Scripting

Scripting

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

Object explorer base state

Drill down to the “Databases” node, and select a database to view database properties. (Also, you can use the column chooser)

select database

Double click on the database, browse out to the “Tables” node and double-click on any table to get the table details

table details

Index Details

index details

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 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”!

panic button

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”

synchronize

Step 02: Notice that both the object explorer and object explorer details now points to the selected object

pointer

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

  1. Using Object Explorer Details and SQL Server Object Search
  2. How to: Search with Wildcards

Nakul Vachhrajani
4 · 36% · 11645
5



Submit

Your Comment


Sign Up or Login to post a comment.

"Getting Started with SSMS - Part 2: Introduction to the Object Explorer and Object Explorer Details Window" rated 5 out of 5 by 5 readers
Getting Started with SSMS - Part 2: Introduction to the Object Explorer and Object Explorer Details Window , 5.0 out of 5 based on 5 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]