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!

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

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

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”

 

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:

• Using Object Explorer Details and SQL Server Object Search – http://msdn.microsoft.com/en-us/library/cc646011.aspx

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.