查找对 SQL Server 数据库中某个对象的所有引用

Question

我正在尝试查找对 SQL Server 数据库中某个对象的所有引用。

如何快速搜索？ SQL Server Management Studio 似乎没有这样做。我使用 http://www.red-gate.com/products/SQL_Search/但我想找到“官方”微软解决方案。是在其他产品中吗？

例如，当我在 Visual Studio 中进行大量搜索时，我希望能够在所有存储过程中找到某些内容。

或者也许我没有以正确的方式编码？

卡尔

Answer 1

使用：

select object_name(m.object_id), m.*
  from sys.sql_modules m
 where m.definition like N'%name_of_object%'

...因为 SYSCOMMENTS 和 INFORMATION_SCHEMA.routines 具有 nvarchar(4000) 列。因此，如果在位置 3998 处使用“name_of_object”，则不会找到它。 SYSCOMMENTS 确实有多行，但 INFORMATION_SCHEMA.routines 被截断。

Answer 2

参加聚会已经很晚了，但是...

您可以使用系统过程sys.sp_depends：

exec sys.sp_depends 'object_name'

结果是一个列出所有依赖于（即引用）object_name 的数据库对象。每行包含引用对象的名称和类型，以及其他信息列，具体取决于 object_name 的类型。

注意：此过程已在 MS SQL Server 2008 中添加。

请参阅：MSDN 文档

文档说此过程可能会在未来版本中删除，并使用 sys.dm_sql_referencing_entities

Answer 3

在 SQL 2008 中添加了 DMV（数据管理功能）sys.dm_sql_referencing_entities。它返回引用您传递给它的对象的任何对象。

SELECT * FROM sys.dm_sql_referencing_entities('dbo.Table1', 'OBJECT')

Answer 4

使用未记录的 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 5

在 SQL Server 2000 中，这里是一个可以在对象定义内部进行搜索的查询，支持最多 2000 个字符的搜索字符串。它使用 syscomments 表中的块。

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 6

我使用此查询来查找存储过程中的所有表（或文本）：

SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'

Answer 7

当您右键单击对象资源管理器中的对象时，SQL Server Management Studio 具有查看依赖关系功能。这是您要找的吗？

Answer 8

如果您想在 SSMS 中使用 OMG Ponies sql 作为键盘快捷键，请将以下 SP 添加到您的主数据库中。

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

然后，您只需将 dbo.SP_FindAllReferences 添加到键盘快捷键中，然后就可以在服务器上的任何数据库的上下文中使用它。

干杯!

注意：如果您使用的是 SQL Server 2005，则必须替换

@sql +=

为

@sql = @sql +

Answer 9

我找到了这样的解决方案..

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_表达式_依赖项/

Answer 10

我不确定“官方微软”方式，但我过去使用过 SqlDigger 。这还不错。

如果您想在 VS 中执行此操作，那么您将需要项目中包含的所有过程的文本。