SQL SERVER – Find Anything in Object Explorer in SSMS

This blog post is a follow up post of an excellent write up by my friend Vinod Kumar. I suggest you read his blog post here SSMS Tips–Object Explorer Details. After reading his blog post, I called him up and had an interesting conversation with him. Here is our conversation:

Pinal: It is easy to find anything in Object Explorer but it is not possible to find everything in Object Explorer.

Vinod: What do you mean? Explain me with example.

Pinal: For example, if I want to find anything related to any schema or table, I can go to Object Explorer and can search about that schema and find relevant information.objectsearch1 SQL SERVER   Find Anything in Object Explorer in SSMS

However, if I want every single object where any string exists, how do I find all of those objects.

Vinod: That is easy as well, remember SQL Server is Microsoft product and will follow the simple rules of the search which you are familiar. Just try to wrap your string with % and see the magic.

Pinal: Never thought of it. Let me try.

After the conversation was over, I tried to wrap my search string with % and it works, indeed it works. SSMS retrieved every single object where the string appeared.

objectsearch2 SQL SERVER   Find Anything in Object Explorer in SSMS

Thanks Vinod. It is so cool that when we have questions we can reach out to experts.

Reference: Pinal Dave (http://blog.sqlauthority.com)

8 thoughts on “SQL SERVER – Find Anything in Object Explorer in SSMS

  1. Hi Pinal,

    I am using SQL Server 2012. When searching from Object Explorer Details I am getting following error.

    Search failed: Failed to retrieve data for this request.

    Any idea why this is coming and how to solve it.



  2. Hi, I’m trying this feature but i get the following error: Search failed. Failed to retrieve data for this request.
    Any suggestion?


  3. One thing I use often but don’t see in the Object Explorer Details is the ability to search for column names.

    SELECT t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name LIKE ‘%Person%’
    ORDER BY schema_name, table_name;

    Is this possible with the Object Explorer?

    Liked by 1 person

  4. This Search box is missing in my case.
    I am using Management Studio 2014. Altough, pressing f7 opens Object Explorer details tab (which is similar to Object Explorer) and it provides a Search box but provides no search results.. always show “Search failed”
    Any help is appreciated!


Leave a Reply

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