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.
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.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.
SELECT sm.object_id AS 'ObjectID',
OBJECT_NAME(sm.object_id) AS 'ObjectName',
o.type_desc AS 'ObjectType',
sm.definition AS 'Contents'
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE sm.definition LIKE '%activity tracking%' --SEARCH STRING
ORDER BY OBJECT_NAME(sm.object_id);
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.
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.
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.