SQL Server tools take the time out of database searches

Serdar Yegulalp, Contributor

Trawling through an entire server’s worth of databases for one thing (or a collection of things) is never enjoyable. I’ve had to do this more than a few times when I knew some routine object was hiding in one database or another, but couldn’t remember for the life of me where it actually was. I’ve tried several different tools to help make sense of the mess, with varying success. 

Two of the better programs I’ve come across are part of the Idera SQL admin toolset: SQL Search and SQL Object Search. These utilities are designed to simplify the process of searching for something in one of your SQL Server databases. The former searches both databases you’ve created and system databases for text within SQL code, such as stored procedures, functions and triggers. The latter looks for objects by name -- tables, stored procedures, functions and views, plus things like column names and key constraints. They’re decently done for quick-and-dirty searches. 

When you fire up SQL Search, you’re presented with a GUI that lets you provide a search term, including possible wildcards, the name of the database and server to search (the default is any database on the local server), what types of objects to search for, and how to limit the search results. By default the results are limited to the first 500 matches, although it’s unclear how those are truncated. In other words, it’s hard to say if these results are simply the first 500 matches returned from any objects regardless of type, or if there’s a specific order in which said objects are searched and returned. 

One thing that might throw you off involves searching by wildcards. When you check the “Use SQL wildcards” box, the wildcard character is the underscore rather than SQL Server’s “%”. For example, to search for everything beginning with “Raster”, you’d use “Raster_”. This is documented in the program’s help file, but it seems counterintuitive to use a wildcard that isn’t SQL Server’s own standard. 

When you perform the search, the results are returned in a columnar view: database, object name, object type, and SQL excerpt. (With SQL Object Search, the columns are object name, type, server, database, parent object, and parent object type.) You can click on any column head to sort by that column, and SQL Search allows you to double-click on any item to see a full-text view of the object in question. If you select a range of results and right-click, you can save the selected results as a CSV or XML-formatted file

One major omission: neither program performs search and replace operations. This by itself makes both apps a lot less useful. The whole of the SQL admin toolkit is available as a 15-day trial download, with the entire kit (24 tools) available for $295. 

Figure 1. The SQL Search function from Idera (click to enlarge)
SQL Search (and its companion product SQL Object Search) lets you hunt through all objects, including system databases, for a particular SQL string.

ABOUT THE AUTHOR
Serdar Yegulalp has been writing about computers and information technology for more than 15 years for a variety of publications, including InformationWeek and Windows Magazine.

View the next item in this Essential Guide: SQL Server BI for the SMB: Prepackaged BI tools vs. do-it-yourself or view the full guide: Exploring third-party SQL Server tools

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: