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.

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.

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

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

6 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?


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