Home | About Nakul Vachhrajani | Archives
January 19, 2012 9:00 AM
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.
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:
The primary functions of the Object Explorer are:
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:
Not only does the object explorer allow an administrator to manage the SQL Server instance, it also allows a developer to:
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.
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. |
 |
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.
In addition to providing object listing functionality same as the Object explorer, the Object Explorer details also provides certain additional information:
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 |  |
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:
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.
It’s time to practice! The practice exercises for Object Explorer and the Object Explorer details are fast, simple and easy.
CREATE PROCEDURE proc_tmpOESearch
AS
BEGIN
PRINT 'BeyondRelational.com'
END
GO
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!
Posted by nakulvachhrajani
Categories: #SQLServer, Blog, Imported from BeyondRelational
Tags: #SQLServer
Mobile Site | Full Site
Get a free blog at WordPress.com Theme: WordPress Mobile Edition by Alex King.
