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