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 (https://blog.sqlauthority.com)
9 Comments. Leave new
Great tip!
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,
Hi, I’m trying this feature but i get the following error: Search failed. Failed to retrieve data for this request.
Any suggestion?
Thanks!
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?
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 ‘%’).
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
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!
Saruchi – I generally use profiler to see the query and error if any.
Search fail when the user doesn’t have rights..