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
- Management
- View activity log information
- Run basic
- Integration Services
- 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!
| Step 01: A. In the Object explorer, right click on the “Tables” B. Go to Filter –> Filter Settings |
 |
| 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) |
 |
| Step 03: Simply click “OK” to apply the filters 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 |
 |
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 |
 |
| Step 03: 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 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 |  |
| Step 02: Under SQL Server Object Explorer, go to “Scripting” |  |
| Step 03: 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 objects.
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 |  |
| 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:
- 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 |  |
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
- 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 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?
- 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?
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!
SQLTwins by Nakul Vachhrajani 