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.
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.