Technical Article

Find Text in All Databases

,

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

/*
Use this script to search all objects, in all databases on the server, that 
are SQL language-defined modules in SQL Server.  These include the following:

DDEFAULT (constraint or stand-alone)
PSQL Stored Procedure
RFReplication-filter-procedure
VView
TRSQL DML Trigger
FNSQL scalar function
IFSQL inline table-valued function
TFSQL table-valued function (2012 - 2016)
RRule (old-style, stand-alone)
*/DECLARE @cmdvarchar(1000),
@search_stringvarchar(200)

CREATE TABLE #temp(
[Database_Name]sysname,
[Schema_Name]sysname,
[Object_Name]sysname,
[Object_Type]nvarchar(60))

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

SET @cmd = 'INSERT INTO #temp SELECT DISTINCT ''?'', s.name AS Schema_Name, o.name AS Object_Name, o.type_desc FROM [?].sys.sql_modules m INNER JOIN [?].sys.objects o ON m.object_id = o.object_id INNER JOIN [?].sys.schemas s ON o.schema_id = s.schema_id WHERE m.definition Like ''%' + @search_string + '%'''

-- Uncomment the following if you have problems with your command and want to see the command
--PRINT @cmd

-- Run for every database on the server
EXEC sp_msforeachdb @cmd

-- Retrieve your results from the temp table
SELECT *
FROM #temp
ORDER BY [Database_Name], [Object_Name], [Object_Type]

-- If you want to omit certain databases from your results, simply add 
-- the appropriate WHERE clause, as in the following:
--SELECT *
--FROM #temp
--WHERE db NOT IN ('DB1', 'DB4', 'DB7')
--ORDER BY db, obj_type, obj_name

DROP TABLE #temp

GO

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating

Share

Join the discussion and add your comment

Share

Rate

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

3.67 (3)

You rated this post out of 5. Change rating

Related content

SQLServerCentral Article

sp_msforeachdb: Improving on an Undocumented Stored Procedure

Have you ever wanted to run a query across every database on a server with the convenience of a stored procedure? If so, Microsoft provided a stored procedure to do so. It’s unreliable, outdated, and somewhat obfuscated, though. Let’s improve on it!

★ ★ ★ ★ ★ ★ ★ ★ ★ ★

4.72 (43)

You rated this post out of 5. Change rating

2016-04-15 (first published: )

16,685 reads