Search DB for all Code that References a Table
SQL Server 2000: Not Supported
SQL Server 2005: Tested
SQL Server 2008: Tested
SQL Server 2008R2: Tested
SQL Server 2012: Not Tested
Date: 14 Sep 2006
It always amazes me how, after so long as a DBA, I can still come across things and wonder 'how did I ever not know that?'
Today's realisation comes in the form of a system view sys.all_sql_modules, which is a concatenation of two other system views and which is a user-friendly listing of all code objects in a database (SPs, functions, views etc).
I suppose it was laziness on my part, as the deprecated syscomments view still works, even in SQL 2008R2, so I've never had to upgrade that part of my knowledge.
Anyway, this piece of code is a quick and dirty search through all code definitions returned by this view. It's not an intelligent search, so it will return instances where variable names or comments contain the search string, but intelligent construction of the search string can help to avoid false positives.
DROP PROCEDURE [dbo].[usp_SearchForObjectReferences]
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
-- Author: jke
-- Create date: 2006-Sep-14
-- Description: Returns a list of Stored Procedures, Views and Functions
-- that contain the SearchCriterium
CREATE PROCEDURE [dbo].[usp_SearchForObjectReferences]
@SearchCriterium nvarchar(MAX) = '' --default will return all objects
SET NOCOUNT ON;
objects.name AS [name],
objects.type_desc AS [type],
all_sql_modules.definition AS sql
INNER JOIN sys.objects
ON objects.object_id = all_sql_modules.object_id
WHERE all_sql_modules.definition LIKE '%' + @SearchCriterium + '%'
AND objects.name != Object_Name(@@Procid) /* Exclude this Stored Procedure from the resultset*/