SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Find Text in All Databases

By Jason Givens,

This script is useful when you need to find all references to something such as a table, view, stored procedure, linked server, or any other object, on a SQL Server.  This script is not the most efficient code but it gets the job done in those times when you simply need to get the job done and it is not a frequent job.  It can also take a littl while, depending on the number of databases on your SQL Server and the number of objects in each database. 

We use this script when we need to move databases, such as for an upgrade, or when we need to change linked server references, or when we are trying to determine if particular objects are still being used\referenced.

This script will allow you to enter a search term and it will search through all of the objects, in all databases on the SQL Server, to find any references to that search term.  To set the search term, simply set the value for the @search_string variable.  This code looks like the following:

-- Set the search string
SET @search_string = 'mytext'

All results are place in a temporary table and the contents of that temp table are returned.  You can modify the temp table query as you like to filter your results.

There is a comment in the code that identifies the type of objects that are searched but here is the list:

D       DEFAULT (constraint or stand-alone)
P       SQL Stored Procedure
RF     Replication-filter-procedure
V       View
TR     SQL DML Trigger
FN     SQL scalar function
IF      SQL inline table-valued function
TF     SQL table-valued function (2012 - 2016)
R       Rule (old-style, stand-alone)

(1/30/2017 - Updated, per a forum request, to include the schema in the results.)

Total article views: 723 | Views in the last 30 days: 11
Related Articles

Script to Find String in Database object

Script that searches every object in a database for a specific string.


Object Search

Procedure to search any database (or a combination of databases, including all) for a specific strin...


Search objects in Database

Use this script to search objects in a Database. Also used to check a tables column types and detail...


Simple Object and String Search within Objects

This Simple script will help to do a Server wide search for an object name or usage of a String in O...


Object Search for Sql Server 2005

This script will search databases, tables, columns, stored procedures for text.

find string    
find text