Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Cost Effective SQL Server Transparent Data Encryption - Free Webinar
 

Using Object Explorer Details and Object Search Feature of SSMS 2008


By:   |   Last Updated: 2010-02-04   |   Comments (2)   |   Related Tips: More > SQL Server Management Studio

Problem

While looking through the new features and improvements in SQL Server 2008 Management Studio (SSMS) we found a potentially interesting one called Object Explorer Details. Database Developers and Administrators can use Object Explorer Details to get a lot of useful information about the object which they have selected in Object Explorer. In this tip I will give you a detailed explanation of how you can use the Object Explorer Details.

Solution

In SQL Server 2008 Management Studio, the Object Explorer Details is a very useful tool when compared to the one which was available in SQL Server 2005. You can use this tool to get detailed information about any object which you have chosen in Object Explorer. You can open up an Object Explorer Details window by either pressing the F7 key or by selecting the "Object Explorer Details" option from the "View" menu as shown in the snippet below.

object explorer details

You can see in the below snippet that when the Database node is selected in object explorer, I get a variety of useful information with respect to all of the databases which are available in this particular instance of SQL Server 2008. The information includes the Database Recovery Model, Compatibility Level, Collation, Owner, Size of Database (MB), Data Space Used (KB), Index Space Used (KB), Space Available (KB) etc. The good thing about this tool is that you can also use this against your SQL Server 2005 instances to see all of this information when you connect to a SQL Server 2005 instance using SQL Server 2008 Management Studio.

sql server management studios

By default, when you open up the Object Explorer Details window you can see five different columns with valuable data. However, this tool supports about 36 different columns. Based on your requirement you can choose any or all columns which are useful to you. You can add new columns by right clicking any one of the column headings and then select the column of interest from the drop down list as shown in the snippet below. One nice feature is that you can even sort any of the rows and can move them in any order you prefer.

object explorer details

Overview of Object Search

Object Search is a very interesting feature which is integrated within the Object Explorer Details window. Let's say if you want to search for all the objects which start or end with a name "Addr", then you can type in the search criteria in the text box and press enter to display the search results as shown in the snippet below.

If you want to search for an object in all the databases then you need to select the Database Node in the Object Explorer window and then provide the name of the object in the text box which needs to be searched for. Once the search has completed you can right click the object which you want to access and then click the Synchronize option from the popup menu to go directly to the location of the object.

object explorer details
Next Steps
  • Next time you need to do some object level searching or investigation, be sure to consider this tip.
  • SQL Server 2008 Management Studio has a number of new features, be sure to check them out.


Last Updated: 2010-02-04


next webcast button


next tip button



About the author
MSSQLTips author Ashish Kumar Mehta Ashish Kumar Mehta has been contributing to the MSSQLTips.com community since 2009 with over 60 tips.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, December 03, 2012 - 11:56:29 AM - CK Back To Top

The same here on SQL Server 2008R2 10.50.4000. I get the same error message against all of the databases on the instance. However, it works fine in SQL Server 2012. No more error message when you search against all DBs on an instance. I test it on 11.0.2218.


Tuesday, February 21, 2012 - 11:49:23 AM - Terry Smith Back To Top

I'm getting error:  Search failed:  Failed to retrieve data for this request

when I focus on all Databases on a server, nothing is returned; only when I go lower to a specific database does the search work


Learn more about SQL Server tools