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.
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?
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:
- 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!
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”!
- 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
- The search is across all object types – Stored Procedures, Views, Indexes and Tables
- The results grid can be sorted and columns can be chosen and rearranged as per requirements
- No 3rd party tools required
- 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/
- Using Object Explorer Details and SQL Server Object Search – http://msdn.microsoft.com/en-us/library/cc646011.aspx
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.
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.
@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).
Thanks for your reply.