Cool Tips‎ > ‎Data Searching‎ > ‎

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


ċ
usp_SearchForObjectReferences.sql
(1k)
Andy Hughes,
Jun 18, 2012, 8:30 AM
Comments