SQL Server tools take the time out of database searches
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
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
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
Dig Deeper
-
People who read this also read...
This was first published in June 2010
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)
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.
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
Join the conversationComment
Share
Comments
Results
Contribute to the conversation