在 SQL Server 中，如何找到引用列的任何地方? [英] In SQL Server, how can I find everywhere a column is referenced?

问题描述

在我相当大的数据库中，我想找出整个模式(SP、函数、表、触发器...)中引用列的任何地方.我不想只进行文本搜索，因为这将获取评论，并且还会从其他表中找到类似命名的列.

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.

有谁知道我是否/如何做到这一点?我使用 SQL Server 2008.

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

推荐答案

警告:尽管这是一种文本搜索方法，但我要分享的脚本拯救了我很多很多小时.它在内部搜索:

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:

• 标量函数
• 表值函数
• 存储过程
• 观看次数
• 触发器

我需要指定一个排序规则才能使其对我有用.

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

输出如下:

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

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

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.:两个查询也在评论内搜索.

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

这篇关于在 SQL Server 中，如何找到引用列的任何地方?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！