在SQL Server中，如何找到引用列的所有位置?(复制)

This question already has an answer here:

这个问题已经有了答案:

• List of Stored Procedure from Table 7 answers
• 表7中存储过程的列表

Within my rather large database, I would like to find out everywhere a column is referenced within the entire schema (SPs, functions, tables, triggers...). I don't want to just do a text search since this will pick up comments and also will find similarly named columns from other tables.

在我相当大的数据库中，我希望找到在整个模式(SPs、函数、表、触发器……)中引用列的所有地方。我不想只做一个文本搜索，因为这将获取注释，并将从其他表中找到类似的命名列。

Does anyone know if/how I can do this? I use SQL Server 2008.

有人知道我能不能做这件事吗?我使用SQL Server 2008。

7 个解决方案

#1

Warning: Even though this is a text-search method, the script I'm going to share has saved me lots and lots of hours. It searches inside:

警告:尽管这是一个文本搜索方法，但是我要分享的脚本为我节省了很多时间。里面搜索:

• scalar functions
• 标量函数
• table-valued functions
• 表值函数
• stored procedures
• 存储过程
• views
• 的观点
• triggers
• 触发器

I needed to specify a collation to make it work for me.

我需要指定一个排序规则以使它适合我。

SELECT sys.objects.object_id, sys.schemas.name AS [Schema], sys.objects.name AS Object_Name, sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    sys.sql_modules.definition COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%{Column Name}%' ESCAPE '\'
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

The output is like the following:

输出如下:

Update: If you need to search for a certain table, SP, etc. you could use a more specialized query:

更新:如果您需要搜索某个表、SP等，可以使用更专业的查询:

DECLARE @SCHEMA_NAME VARCHAR(100) = 'dbo';
DECLARE @OBJECT_NAME  VARCHAR(100) = 'MY_OBJECT';

SELECT
    sys.objects.object_id,
    sys.schemas.name AS [Schema], 
    sys.objects.name AS Object_Name, 
    sys.objects.type_desc AS [Type]
FROM sys.sql_modules (NOLOCK) 
INNER JOIN sys.objects (NOLOCK) ON sys.sql_modules.object_id = sys.objects.object_id 
INNER JOIN sys.schemas (NOLOCK) ON sys.objects.schema_id = sys.schemas.schema_id
WHERE
    (
           '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].'+@OBJECT_NAME+'[^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]'+@SCHEMA_NAME+'.\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
        OR '#' + sys.sql_modules.definition + '#' COLLATE SQL_Latin1_General_CP1_CI_AS LIKE '%[^a-z_]\['+@SCHEMA_NAME+'\].\['+@OBJECT_NAME+'\][^a-z_]%' ESCAPE '\'
    )
ORDER BY sys.objects.type_desc, sys.schemas.name, sys.objects.name

P.S.: Both queries search inside comments too.

注::这两个查询都在评论中搜索。

#2

Best ways to do it are described in this article.

本文描述了最佳的实现方法。

A sample:

一个示例:

SELECT OBJECT_NAME (referencing_id),
              referenced_database_name, 
       referenced_schema_name, referenced_entity_name
FROM sys.sql_expression_dependencies d
WHERE OBJECT_NAME(d.referenced_id) = 'TableName'
      AND OBJECT_DEFINITION (referencing_id)  LIKE '%ColumnName%'
ORDER BY OBJECT_NAME(referencing_id);

#3

i tried this query and it seems to be fine:

我尝试了这个问题，似乎没有问题:

select 
obj.type REFERENCING_OBJECT_TYPE
 ,SCHEMA_NAME(obj.schema_id) REFERENCING_OBJECT_SCHEMA
 ,obj.name                  REFERENCING_OBJECT_NAME
from sysdepends x
INNER JOIN sys.objects obj ON x.id  = obj.object_id
where depid = object_id('yourSchema.yourTable')
and col_name(depid, depnumber) = 'yourColumn'

#4

As Luv said this is an old question but I've found two more solutions that may be helpful.

正如Luv所说，这是一个老问题，但我找到了另外两个可能有用的解决方案。

I'm using the sys.dm_sql_referenced_entities system object that finds all referenced objects and columns in a specified object. You can use the following query:

我使用系统。dm_sql_referenced_entity系统对象，该对象查找指定对象中的所有引用对象和列。您可以使用以下查询:

SELECT DISTINCT 
referenced_schema_name AS SchemaName, 
referenced_entity_name AS TableName, 
referenced_minor_name  AS ColumnName
FROM sys.dm_sql_referenced_entities ('yourrefencingobject', 'OBJECT'); 
GO

Which gives the following result:

结果如下:

Downside of this object is that you'll need to specify a referencing object.

这个对象的缺点是您需要指定一个引用对象。

Or do a search like:

或者做这样的搜索:

SELECT DISTINCT object_name(id) 
FROM AdventureWorks2012.dbo.syscomments (nolock) 
WHERE text like '%BusinessEntityID%'

Which gives the following result:

结果如下:

I've also find the following SP that you could use in this article, but haven't tested it properly:

我还找到了下面的SP，你可以在本文中使用，但是还没有进行正确的测试:

> DECLARE    @string varchar(1000),   @ShowReferences char(1)
> 
> SET @string = 'Person.Person.BusinessEntityID' --> searchstring
> 
> SET @ShowReferences = 'N'
> /****************************************************************************/ /*                                                                    
> */ /* TITLE:   sp_FindReferences                                               */ /*                                                                          */ /* DATE:    18 February, 2004                                               */ /*                                                                          */ /* AUTHOR:  WILLIAM MCEVOY                                                  */ /*                                                                          */ /****************************************************************************/ /*                                                                    
> */ /* DESCRIPTION:  SEARCH SYSCOMMENTS FOR INPUT STRING, OUTPUT NAME OF OBJECT */ /*                                                       
> */ /****************************************************************************/ set nocount on
> 
> declare @errnum         int         ,
>         @errors         char(1)     ,
>         @rowcnt         int         ,
>         @output         varchar(255)
> 
> select  @errnum         = 0         ,
>         @errors         = 'N'       ,
>         @rowcnt         = 0         ,
>         @output         = ''        
> 
> /****************************************************************************/ /* INPUT DATA VALIDATION                                              
> */ /****************************************************************************/
> 
> 
> /****************************************************************************/ /* M A I N   P R O C E S S I N G                                      
> */ /****************************************************************************/
> 
> -- Create temp table to hold results DECLARE @Results table (   Name        varchar(55),   Type        varchar(12),   DateCreated datetime,  
> ProcLine    varchar(4000) )
> 
> 
> IF (@ShowReferences = 'N') BEGIN   insert into @Results   select
> distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO
>     join syscomments SC on SC.id = SO.id    where SC.text like '%' + @string + '%'   union   select distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO    where SO.name like '%' + @string + '%'   union   select distinct
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          ''
>     from sysobjects  SO
>     join syscolumns SC on SC.id = SO.ID    where SC.name like '%' + @string + '%'    order by 2,1 END ELSE BEGIN   insert into @Results  
> select 
>          'Name'      = convert(varchar(55),SO.name),
>          'Type'      = SO.type,
>          crdate,
>          'Proc Line' = text
>     from sysobjects  SO
>     join syscomments SC on SC.id = SO.id    where SC.text like '%' + @string + '%'   union   select 
>          'Name'      = convert(varchar(55),SO.name),
>          'Type'      = SO.type,
>          crdate,
>          'Proc Line' = ''
>     from sysobjects  SO    where SO.name like '%' + @string + '%'   union   select 
>          'Name' = convert(varchar(55),SO.name),
>          'Type' = SO.type,
>          crdate,
>          'Proc Line' = ''
>     from sysobjects  SO
>     join syscolumns SC on SC.id = SO.ID    where SC.name like '%' + @string + '%'    order by 2,1 END
> 
> IF (@ShowReferences = 'N') BEGIN   select Name,
>          'Type' = Case (Type)
>                     when 'P'  then 'Procedure'
>                     when 'TR' then 'Trigger'
>                     when 'X'  then 'Xtended Proc'
>                     when 'U'  then 'Table'
>                     when 'C'  then 'Check Constraint'
>                     when 'D'  then 'Default'
>                     when 'F'  then 'Foreign Key'
>                     when 'K'  then 'Primary Key'
>                     when 'V'  then 'View'
>                     else Type
>                   end,
>          DateCreated
>     from @Results
>     order by 2,1 END ELSE BEGIN   select Name,
>          'Type' = Case (Type)
>                     when 'P'  then 'Procedure'
>                     when 'TR' then 'Trigger'
>                     when 'X'  then 'Xtended Proc'
>                     when 'U'  then 'Table'
>                     when 'C'  then 'Check Constraint'
>                     when 'D'  then 'Default'
>                     when 'F'  then 'Foreign Key'
>                     when 'K'  then 'Primary Key'
>                     when 'V'  then 'View'
>                     else Type
>                   end,
>          DateCreated,
>          ProcLine
>     from @Results
>     order by 2,1 END

Hope this helps

希望这有助于

#5

Hello Although this is an old post I was able to combine few tips from above got something like this below which was help full for me. The reason had I create this one is the column I was in lot of tables so it did not provide me clear output.

你好，虽然这是一个旧的帖子，我可以结合一些技巧从上面得到如下的东西，帮助我填满。我创建这个的原因是我在很多表中的列，所以它没有提供清晰的输出。

SELECT 
      SCHEMA_NAME(schema_id)+'.'+[name] as objectname
      ,type_desc
     ,referenced_schema_name AS SchemaName
,referenced_entity_name AS TableName
,referenced_minor_name  AS ColumnName
  FROM [sys].[all_objects] ob cross apply sys.dm_sql_referenced_entities ( SCHEMA_NAME(schema_id)+'.'+[name], 'OBJECT') e

  where is_ms_shipped = 0 and type_desc in ('AGGREGATE_FUNCTION'
,'SQL_SCALAR_FUNCTION'
,'SQL_INLINE_TABLE_VALUED_FUNCTION'
,'SQL_STORED_PROCEDURE'
,'SQL_TABLE_VALUED_FUNCTION'
,'SQL_TRIGGER'
,'VIEW')
and name !='sp_upgraddiagrams'
and referenced_entity_name  = 'table name'
and referenced_minor_name = 'columnname'

#6

I would like to avoid using sys.sql_dependencies because this feature will be removed in a future version of Microsoft SQL Server. I also cannot use OBJECT_DEFINITION function because all my objects are encrypted. So I came up with the following query which seems pretty simple and works for me so well that I even wrapped it to the function:

我想避免使用sys。sql_dependencies因为这个特性将在Microsoft SQL Server的未来版本中被删除。我也不能使用OBJECT_DEFINITION函数，因为所有对象都是加密的。所以我想到了下面的查询，这个查询看起来非常简单，而且对我来说非常有用，我甚至把它封装到函数中:

-- =============================================
-- Description: Gets all the stored procedures, functions and triggers referencing the specified column.
-- Example:     SELECT * FROM dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN(N'dbo', N'MY_TABLE', N'MY_COLUMN');
-- =============================================
CREATE FUNCTION dbo.UFN_GET_SP_FN_TR_REFERENCING_COLUMN 
(
    @SchemaName sysname,
    @TableName sysname,
    @ColumnName sysname
)
RETURNS TABLE
AS 
RETURN

    SELECT R.referencing_schema_name + N'.' + R.referencing_entity_name AS referencing_entity_name
    FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R 
        INNER JOIN sys.objects AS O 
            ON R.referencing_id = O.object_id
    WHERE O.[type] IN ('FN'/*SQL scalar function*/, 'IF'/*SQL inline table-valued function*/, 'TF'/*SQL table-valued-function*/, 'P'/*SQL Stored Procedure*/, 'TR' /*SQL DML trigger*/)
          AND EXISTS(SELECT 1 FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName);

GO

#7

Here's a slight tweak on @alex's TV-UDF to include views also:

下面是@alex的TV-UDF的一个小调整，包括视图:

/*
    Source: https://*.com/a/47775531/852956
    Gets all the stored procedures, functions and triggers referencing the specified column.

    SELECT * FROM Utility.ft_SelectSprocFuncAndTrigrRefs(N'BrakeRotor', N'BrakeRotors', N'BrakeRotorNumber');
*/
CREATE FUNCTION Utility.ft_SelectSprocFuncAndTrigrRefs
(
    @SchemaName sysname,
    @TableName sysname,
    @ColumnName sysname
)
RETURNS TABLE
AS 
RETURN

    SELECT QUOTENAME(R.referencing_schema_name) + N'.' + QUOTENAME(R.referencing_entity_name) AS ReferencingEntityName
    FROM sys.dm_sql_referencing_entities(@SchemaName + N'.' + @TableName, 'OBJECT') AS R 
        INNER JOIN sys.objects AS O 
            ON R.referencing_id = O.object_id
    WHERE O.[type] IN (
                'FN'/*SQL scalar function*/,
                'IF'/*SQL inline table-valued function*/,
                'TF'/*SQL table-valued-function*/,
                'P'/*SQL Stored Procedure*/,
                'TR' /*SQL DML trigger*/
            )
          AND EXISTS(
                    SELECT 1
                    FROM sys.dm_sql_referenced_entities (R.referencing_schema_name + N'.' + R.referencing_entity_name, 'OBJECT') AS RE
                    WHERE RE.referenced_entity_name = @TableName AND RE.referenced_minor_name = @ColumnName)

    UNION SELECT QUOTENAME(VIEW_SCHEMA) + N'.' + QUOTENAME(VIEW_NAME) AS ReferencingEntityName
        FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
        WHERE TABLE_SCHEMA = @SchemaName
          AND TABLE_NAME = @TableName
          AND column_name = @ColumnName
GO