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:
- Database Engine instances
- Analysis Services
- Integration Services
- Reporting Services
- 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.
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:
SQL Server instance – allows you to:
- Manage SQL Server configuration
- Manage part of the SQL Server surface configuration
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
Security – allows you to:
- Manage SQL Server instance security, including logins
Server Objects – allows you to:
- Manage endpoints
- Manage devices
- Manage linked servers
Replication – allows you to:
- Manage & configure replication
- View activity log information
- Run basic
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:
- Filter & identify database objects from all objects of a database
- 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!
A. In the Object explorer, right click on the “Tables”
B. Go to Filter –> Filter Settings
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)
Simply click OK to apply the filters in the Object Explorer
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
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:
Right click on any object.
Go to Script Table As –> Create To –> New Query Editor Window
A script that can be used to recreate a database object is now generated.
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:
Go to Tools –> Options
Under SQL Server Object Explorer, go to Scripting
Configure scripting options of your choice depending upon your project/organization best practices
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:
- Database information
Information about database owner, space available, recovery model and other database properties
- Table & index usage
Information about # of rows in a table/index, size of table/rows, etc
- 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
The following screenshots demonstrate how you can move about the object explorer details window via a drill-down mechanism.
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)
Double click on the database, browse out to the “Tables” node and double-click on any table to get the table details
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:
- Navigate to the Object Explorer Details window by using the View menu or pressing the (F7) key when in the SQL Server Management Studio
- Drill down to the database against which the search needs to be carried out
- In the Search bar, enter the object name that you would like to search for (you can use wild-card characters)
- Simply hit “Enter” to execute the 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”!
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”
Step 02: Notice that both the object explorer and object explorer details now points to the selected 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.
- Explore how to start and stop the SQL Server and the SQL Server Agent services via the Object Explorer window
- From the AdventureWorks2008R2 database, attempt to script at least 2 similar objects (stored procedures, triggers, etc) to a single script together
Object Explorer Details
Create the following stored procedure within the AdventureWorks2008R2 database
CREATE PROCEDURE proc_tmpOESearch
- 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?
Search for the object names using wild cards – “dm_os%counters” within the AdventureWorks2008R2 database
- Can you search for objects using the .NET regular expressions?
- 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?
- Using Object Explorer Details and SQL Server Object Search
- How to: Search with Wildcards