Searching All Objects Within A Database For A Specific Text String

Finding a particular text string within a database object can be a bit tricky if you’ve never done it before.  Unfortunately, SQL Management Studio does not provide any useful tools for doing this.  Though it’s not something you will likely find yourself doing very often, there are times when you may need to check to see if a particular text string exists within any objects on your database.

This scenario occurred for me recently when migrating a database to new hardware.  As a result of the migration, the server name and named instance of the database was going to change.  Since there were quite a few reports that had been built over the years to pull data from this database I would need to modify any stored procedures that had the server and database connection strings hard coded.  Before I could do that I first would need to identify them.

In order to accomplish this task, my first step was to note all the linked servers that were configured on the old database instance.  A linked server is configured to allow the database engine to execute SQL commands against tables in another instance of SQL Server.  Any objects that required an update to a hard coded connection string would reside on linked servers that were configured in the old database.  The verify which servers are linked on a particular server expand Server Objects–Linked Servers.

LinkedServers

Now that I know which servers are linked, I now know which ones I need to review to determine if any database objects contain the hard coded connection string.

The script below will query the sys.sql_modules and sys.objects system tables and look for the specified search string within the definition column of sys.sql_modules.  This column contains the actual code of the object.

In this example I am searching the DYNAMICS database for any object that contains the string activity tracking.  For this example my query returned a single result, as shown below.

ObjectSearch_results

Here you will see the ObjectName as well as the Type and Contents of the object.  From here it will be up to you to open each individual object and modify it as needed.  Keep in mind, that with this method you will need to run the script against each table within each linked server to ensure you locate all applicable database objects.  It can be a bit tedious depending on the number of databases on each server.

Another method for searching database objects for a particular text string is to use 3rd party software such as RedGate SQL Search or Idera Admin Toolset.  Though both tools will allow you to search across all databases on a SQL Server at once,  I prefer the SQL Search tool within the Idera Admin Toolset as it seems to run faster and provides a smoother interface for accomplishing this task.

The screenshot below shows the Idera SQL Search tool in use.

Idera_SQLSearch_sh

In this example I am searching for the same text string as above, activity tracking, but have left the Database field blank so that it searches across all databases on the LAB-SQL2012 server.  You can also specify the object type and search options within Idera SQL Search to allow for more targeted searching.