SSMS is a nice tool for database developers. However, it is far from being ideal, and this is because some routine operations in SSMS are extremely inconvenient to perform. Let us learn about dbForge Object Search for SQL Server.
Very often, developers face the challenge of searching a database for all occurrences of an object, column, variable, or search simultaneously all the objects, in which a search value occurs. If you happen to solve a similar problem, then you must know that this problem is not the most trivial and Ctrl + F will not help here.
Of course, you can write a quick metadata query:
SELECT SCHEMA_NAME(o.[schema_id]), o.name, o.type_desc, m.[definition] FROM sys.sql_modules m JOIN sys.objects o ON m.[object_id] = o.[object_id] WHERE [definition] LIKE '%PersonID%'
However, not all developers know metadata structure … and why waste time writing a query, if you already have an excellent free plug-in that helps to effectively search the wilds of DDL.
Though dbForge Object Search for SQL Server has been released not so long ago, it occupied the place of pride in my gentleman’s set.
This plug-in impresses me with the simplicity of use — type a text in the search box and click Enter:
All the search results are displayed in a table that supports filtering. When you select a respective object, its DDL is displayed below… but not as a simple text. The plug-in has a convenient syntax highlighting.
If you need to restrict your search, you can configure the filtering by object type. For example, we can search only within stored procedures and triggers.
Additional filters allow you to search much faster.
dbForge Object Search for SQL Server does not cache the information between the searches and directly accesses metadata. For me, it’s definitely a plus, especially when in active development and continuously updating database schema — you don’t need to constantly press Refresh cache to get the proper search results.
If necessary, you can do a search on multiple databases at the same time:
The plug-in supports navigation. Simply select the context menu command Find in Database Explorer, and you will automatically jump to the found object:
When working with this plug-in, I discovered some pleasant things. For example, previous search queries are saved in the search history:
If we talk about the object search, doing it in SQL Server Management Studio is rather inconvenient. Queries dealing with this task are inefficient and require deep knowledge of the SQL Server system objects. By contrast, dbForge Object Search for SQL Server does the task brilliantly.
Reference: Pinal Dave (https://blog.sqlauthority.com)
