Search DB for all Code that References a Table

Applicability:

SQL Server 2000: Not Supported

SQL Server 2005: Tested

SQL Server 2008: Tested

SQL Server 2008R2: Tested

SQL Server 2012: Not Tested

Credits:

Author: jke

Date: 14 Sep 2006

Description

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.

Code

DROP PROCEDURE [dbo].[usp_SearchForObjectReferences]

GO

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

GO

-- =============================================

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

AS

BEGIN

SET NOCOUNT ON;

SELECT

objects.name AS [name],

objects.type_desc AS [type],

all_sql_modules.definition AS sql

FROM sys.all_sql_modules

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*/

ORDER BY

[type],

[name]

END