Find all references to an object in an SQL Server

Question

I'm trying to find all references to an object in an SQL Server database.

How can I quickly search? SQL Server Management Studio does not seem to do it. I use http://www.red-gate.com/products/SQL_Search/ but I'd like to find the "official" Microsoft solution to this. Is it in another product?

For example, when I do a mass search in visual studio, I would like to be able to also find something in all stored procedures.

Or maybe I'm not coding this the right way?

Carl

Answer 1

If you want to use OMG Ponies sql as a keyboard shortcut in SSMS, add the following SP to your master db.

USE [master]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[SP_FindAllReferences]
@targetText nvarchar(128)
AS
BEGIN
    SET NOCOUNT ON;

    declare @origdb nvarchar(128)
    select @origdb = db_name()

    declare @sql nvarchar(1000)

    set @sql = 'USE [' + @origdb +'];' 
    set @sql += 'select object_name(m.object_id), m.* '
    set @sql += 'from sys.sql_modules m  where m.definition like N' + CHAR(39) + '%' + @targetText + '%' + CHAR(39)

    exec (@sql)

    SET NOCOUNT OFF;
END

Then you just need to add dbo.SP_FindAllReferences to your keyboard shortcuts and then you can use it in the context of any DB on your server.

Cheers!

NB: If you are using SQL Server 2005 you will have to replace

@sql +=

with

@sql = @sql +

Answer 2

I'm not sure of 'official microsoft' way but I've used SqlDigger in the past. It's not bad.

If you want to do it in VS, then you will need the text of all your procs included in your project.

Answer 3

SQL Server Management Studio has a View Dependencies feature when you right click on an object in the Object Explorer. Is this what you're looking for?

Answer 4

In SQL Server 2000 here is a query that can search inside object definitions, supporting search strings of up to 2000 characters. It uses the chunks in the syscomments table.

SELECT O.name, O.xtype
FROM sysobjects O
WHERE EXISTS (
   SELECT *
   FROM
      (
         SELECT
            Chunk = Substring(C1.text, T.Offset, 4000)
                + Coalesce(Substring(C2.text, 1, T.AdditionalLength), '')
         FROM
            syscomments C1
            CROSS JOIN (
               SELECT 1, 0
               UNION ALL
               SELECT 2001, 2000
            ) T (Offset, AdditionalLength)
            LEFT JOIN syscomments C2
               ON C1.id = C2.id
               AND C1.colid + 1 = C2.colid
               AND T.Offset > 1
         WHERE
            O.id = C1.id
      ) C
   WHERE
      Chunk LIKE '%search string%'
);

Answer 5

With the use of an undocumented SQL sp: sp_msforeachdb

exec sp_msforeachdb '
USE [?];

--IF DB_NAME() NOT IN (''master'',''tempdb'',''model'',''msdb'')
BEGIN
DECLARE 
    @SearchStr varchar(100) 
    SET @SearchStr = ''%column_store_segments%''; 
SELECT DISTINCT
    ''?'' as db_name, o.name
    , ( CASE upper(o.xtype) 
            WHEN ''C'' THEN ''CHECK constraint''        
            WHEN ''D'' THEN ''Default or DEFAULT constraint''                       
            WHEN ''F'' THEN ''FOREIGN KEY constraint''  
            WHEN ''L'' THEN ''Log''                                                                 
            WHEN ''FN'' THEN ''Scalar function''        
            WHEN ''IF'' THEN ''Inline table-function''
            WHEN ''PK'' THEN ''PRIMARY KEY or UNIQUE constraint''
            WHEN ''P'' THEN ''Stored procedure''                                            
            WHEN ''R'' THEN ''Rule''                    
            WHEN ''RF'' THEN ''Replication filter stored procedure''            
            WHEN ''S'' THEN ''System table''            
            WHEN ''TF'' THEN ''Table function''
            WHEN ''TR'' THEN ''Trigger''                
            WHEN ''U'' THEN ''User table''      
            WHEN ''V'' THEN ''View''                    
            WHEN ''UQ'' THEN ''UNIQUE constraint (type is K)''                  
            WHEN ''X'' THEN ''Extended stored procedure''                           
        ELSE upper(o.xtype) END ) Type
    , ( CASE upper(o.xtype)
            WHEN ''PK'' THEN ( select object_name(parent_object_id) FROM sys.key_constraints (nolock) WHERE o.name=name )
            WHEN ''F'' THEN ( select object_name(parent_object_id) FROM sys.foreign_keys (nolock) WHERE o.name=name )
            WHEN ''TR'' THEN ( select object_name(parent_id) FROM sys.triggers (nolock) WHERE o.name=name )     
        ELSE '''' END ) as Parent_Object
FROM sysobjects o (nolock) 
INNER JOIN syscomments sc (nolock) ON o.id = sc.id
WHERE UPPER( text ) LIKE UPPER( @SearchStr ) AND substring(o.name,1,3)<> ''dt_'' 
GROUP BY o.name, o.xtype
END'
GO

Answer 6

I found a solution like this..

USE [Database]
GO

SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = 'SP_Pay_GetData'
order by referencing_object_name

http://blog.sqlauthority.com/2012/12/02/sql-server-find-referenced-or-referencing-object-in-sql-server-using-sys-sql_expression_dependencies/