Different Ways to Search for SQL Server Database Objects


By:   |   Updated: 2010-03-17   |   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


Last Updated: 2010-03-17


get scripts

next tip button



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

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





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

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


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

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

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
Fantastic article! Just added these SPs locally!


download


Recommended Reading

How to read the SQL Server Database Transaction Log

How to Attach a SQL Server Database without a Transaction Log and with Open Transactions

How to rename a SQL Server database

Different ways to determine free space for SQL Server databases and database files

How to determine SQL Server database transaction log usage





get free sql tips
agree to terms


Learn more about SQL Server tools