Object Explorer Details (Part II) – Underappreciated features of Microsoft SQL Server


As mentioned in my previous post, I am currently developing a series of brief articles on the many underappreciated features of Microsoft SQL Server as received from the community as a response to Andy Warren’s SQLServerCentral.com’s editorial (http://www.sqlservercentral.com/articles/Editorial/71788/), which was published on December 17, 2010.

We started this journey by looking at the Object Explorer Details window(Object Explorer Details (Part I) – Underappreciated features of Microsoft SQL Server). I am sure that by now, most of you would have explored the Object Explorer details window and would have started to get used to it as part of your daily work life.

Searching in the Object Explorer Details Window

Today, we will be doing something a bit more fun and productivity enhancing. Imagine the following scenario.

Scenario:

You want to change the data-type of a column in your database, but are not sure about the impacted areas. You don’t have (or don’t have access to) any database documentation and don’t have the time to create any. What do you do?

Solution:

The Microsoft SQL Server 2008 Management Studio gives you a fast and easy way to do this very quickly and easily via the Object Explorer Details window. 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!

image

Simply right-clicking on a specific search result and choosing “Synchronize” navigates to the particular object in the Object Explorer!

Right click on a result Click Synchronize
image image

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

image

Salient Features/Benefits:

  1. Copy results over to Excel – Select the set of records required (or use “Ctrl+A” for selecting on the results) and use “Ctrl+C”. Doing so puts the tab-separated results (with the headers) on the clipboard, and pasting these in Excel maintains all columns
  2. The search is across all object types – Stored Procedures, Views, Indexes and Tables
  3. The results grid can be sorted and columns can be chosen and rearranged as per requirements
  4. No 3rd party tools required
  5. No knowledge of SQL Server internals required

A drawback & Red Gate SQL Search

The search feature is good, but not complete when used out-of-the-box from the Microsoft SQL Server Management Studio. The one feature that does not come is a contains search. The search searches through the object names, but does not search within the objects. Let’s see an example:

CREATE PROCEDURE proc_tmpNakul
AS
BEGIN
    PRINT 'Employee'
END

Let’s try searching for “Employee” and for “Nakul”.

Searching for “Employee” Searching for “Nakul”
image image

 

Red Gate Software has developed a free tool for more sophisticated or in-depth searching. You can evaluate this tool, SQL Search by downloading it for free at:http://www.red-gate.com/products/sql-development/sql-search/

More Resources:

In conclusion:

There are a lot of other things that can be done with the Object Explorer details window. You can view dependencies, confirm whether or not an object is matching up to preset policies and a lot more. The Object Explorer Details described in Part I, along with the using Red-Gate SQL Search make for very powerful database navigation and search.

In the next post, I will be looking at how to filter objects within the Object Explorer itself.

Have a good day!

Be courteous. Drive responsibly.

Advertisements

3 thoughts on “Object Explorer Details (Part II) – Underappreciated features of Microsoft SQL Server

  1. balakrishna141

    HI Nakul,

    Really am wondering and also getting thrill.

    Just one point i wanted to add that you can not say it is a drawback when its resulting only Objects.From your example Nakul is an Object so its giving result, when you come to employee its a description( i mean detail ness of that object) of that Object right.

    So i meant to say that we need to search Object only when ever we really wants to see what is there inside of the that Object .

    Please correct me if am wrong.

    Thanks a lot for sharing such kind of silent features.

    Like

    Reply
  2. Nakul Vachhrajani

    @BalaKrishna: Thank-you for your comments and I am glad that you like my posts and are finding them useful. The drawback is in the sense that one cannot use the object explorer details for dependency checking (for example, the results will not return the stored procedure – proc_tmpNakul even though it might be using the “Employee” table/object simply because the stored procedure does not contain the word “Employee” in the name).

    Like

    Reply

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