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: 624 | Views in the last 30 days: 12
 
Related Articles
SCRIPT

Script to Find String in Database object

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

SCRIPT

Object Search

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

SCRIPT

Search objects in Database

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

SCRIPT

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

SCRIPT

Object Search for Sql Server 2005

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

Tags
find string    
find text    
search    
sp_msforeachdb    
 
Contribute