Different Ways to Search for SQL Server Database Objects

By:   |   Comments (4)   |   Related: More > Database Administration


Problem

In the course of any database development project, searching for database objects is something you will probably do.  It can be any kind of search which is done using some text as the search mechanism. Some of the examples of search that a developer / DBA may make within a database or database server are: searching for a database object, searching for occurance of particular text within database objects, searching within the schema of a database object, search within the results of query results or entire tables, etc..

In this tip, we look at different mechanisms that can be used to facilitate this type of searching.

Solution

Searching is generally required across database objects, across databases, and also across database servers. There are different mechanism that can be used for different kinds of search requirements.  For this tip I will be using SQL Server Management Studio (SSMS) 2008.

We will look at four different ways for our search that cover various search requirements.

  1. SSMS Object Search
  2. T-SQL Scripts
  3. SQL Search Add-in
  4. SSMS Tools Pack Add-in

1) SSMS Object Search:

As a part of the enhancements in SSMS 2008, object explorer details window has a nice object search toolbar which allows context sensitive searching of database objects. As show in the below screenshot, I attempted searching database object named "Address".  It fetched all the tables that it found with the same name. But there can be many database object having the text "Address" within it, or "Address" text can also appear in the definition of the database object. Also you can use '%' as a wild card character along with the text that you want to search in the same way as you use it with "like" for T-SQL.

As a part of the enhancements in SSMS 2008, object explorer details window has a nice object search toolbar

2) T-SQL Scripts:

This one is the most flexible to search anything you would like to search within your database server. Using this mechanism, you have the flexibility to interrogate any schema or data level information that you would like, but the only down-side is that you need to create code and create a set of scripts to search across databases. You can create parameterized stored procedures to faciliate the search. But if a change is required, either you need to create your stored procedures with all the parameters based on how you would like to slice and dice your search or you will need to change the code.

In the below screen-shot, I attempted searching the text "Address" within "AdventureWorks" database. I found a stored-procedure containing "Address" text within it's name. Then I created a query to search text "Address" within all the stored-procedures in this database. There is no limit to the kind of scripts that can be created for searching using this technique as long as you know which catalog views to use.

T-SQL Scripts

3) SQL Search Add-in:

This is a free SSMS add-in from Red Gate Software and can be downloaded from here. Once installed, it becomes visible on the toolbar. This search requires no explanation and the benefit is that you just type the text and see the results. You can also limit the scope for a database or type of database objects as per your requirement. It also shows you if the text you are searching for appeared in the name of the database object and definition of the database object.

In the screenshot below, you can see that I attempted searching "Address" keyword within "AdventureWorks" database, and then tried locating the stored procedure that we looked at using the T-SQL Script. It shows this stored procedure twice in the search result, as "Address" is a part of the name of this stored procedure and also "Address" appears in the definition of the stored procedure. Not only does it list the object, but also highlights the places where the text appeared in the definition.

The only downside is that it won't can not copy the search results from the grid. I have requested this feature from Red Gate Software and I heard back that there is a good possibility that this feature will get added.

 SQL Search Add-in

4) SSMS Tools Pack Add-in:

This is another free SSMS add-in and can be downloaded from here. After installation, it can be accessed from the object explorer by right-clicking as shown below.  Also at context specific areas, it is availalbe by right-clicking. If we extend our search to the next level, say we want to search the entire database including all tables and every column for a text that contains "Napa", and we need a report of the same. See the screen-shot below, where I did a right-click on database and did a search on the entire database data for any text that contains the word "Napa". This add-in has a lot of featuers, I leave it to the reader to browse the rest of these features.

SSMS Tools Pack Add-in
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Siddharth Mehta Siddharth Mehta is an Associate Manager with Accenture in the Avanade Division focusing on Business Intelligence.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips



Comments For This Article




Thursday, August 29, 2013 - 6:23:19 AM - EI Back To Top (26521)

You can do this using procedure....http://exuberantindia.com/?p=177


Wednesday, March 17, 2010 - 12:37:59 PM - Wingenious Back To Top (5069)

There's another free option for searching among SQL Server database objects, DBGizmo. It operates outside of SSMS, but it does a lot more than just searching.

There's a search feature for names of database objects. You can quickly find any string within names of tables, columns, functions, and other objects.

There's a search feature for SQL code database objects. You can quickly find any string within your SQL routines and the matches are shown in context.

There's a scripting feature for SQL code database objects. You can script out selected objects and the SQL routines are in correct dependency order.

There's a unique interactive database diagram to visualize table relationships. You can quickly see where any given table fits within the hierarchy.

There's a friendly editor for the extended properties of various database objects. You can easily embed your documentation into the database itself.

This handy tool is absolutely FREE, but any feedback would be appreciated.

DBGizmo can be downloaded here... http://www.DBGizmo.net


Wednesday, March 17, 2010 - 10:35:29 AM - siddhumehta Back To Top (5068)

I am glad you found the article useful. I didn't get the meaning of the second sentence "Just installed these SPs locally" ?


Wednesday, March 17, 2010 - 10:02:23 AM - nosnetrom Back To Top (5067)
Fantastic article! Just added these SPs locally!














get free sql tips
agree to terms