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.SQL SERVER - Find Anything in Object Explorer in SSMS objectsearch1

Solarwinds

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.

SQL SERVER - Find Anything in Object Explorer in SSMS objectsearch2

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

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

Solarwinds
Previous Post
MySQL – Profiler : A Simple and Convenient Tool for Profiling SQL Queries
Next Post
SQL SERVER – Dude, Where is the SQL Agent Job History? – Notes from the Field #017

Related Posts

9 Comments. Leave new

  • Great tip!

    Reply
  • 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.

    Thanks,

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

    Reply
  • 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?

    Reply
  • Christopher J. McClellan
    February 27, 2014 6:07 pm

    The filter in the object explorer works pretty well too. It gives you a “contains” option (which is essentially just wrapping your search criteria in ‘%’).

    Reply
  • I suggest everyone to get the FREE Red Gate SQL Search, beats SSMS search (which never worked for me, like aboe reply)

    SQL Search searches objects, and now even SQL jobs
    https://documentation.red-gate.com/ss1/release-notes-and-other-versions/sql-search-beta-program

    Reply
  • 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!

    Reply
  • Search fail when the user doesn’t have rights..

    Reply

Leave a Reply

Menu