查找要查看的所有参考文献 [英] Find All References to View

问题描述

我有各种数据库，可以确定的是我正在删除一些真正孤立的东西(在这种情况下是一个视图).要使用的 SQL 是否正确:

I've got various databases, and what to be sure I am removing something (a view in this case) that is truly orphaned. Is the the correct SQL to be using:

SELECT r.routine_name, 
       r.routine_definition
  FROM INFORMATION_SCHEMA.ROUTINES r
 WHERE r.routine_definition LIKE '%my_view_name%' 

它的问题在于这些引用没有选择存储过程中的声明，我不知道还有什么.

The problem with it is that these references aren't picking up declarations in stored procedures, and I don't know what else.

我找到了 SO 问题 我记得，但这也无济于事.这:

I found the SO Question I'd remembered, but it's not helping either. This:

SELECT t.*
  FROM SYSCOMMENTS t
 WHERE CHARINDEX('my_view_name', t.text) > 0

...很接近.我获得了我知道正在使用该视图的存储过程的主体，但我无法获得实际的过程名称.

...is close. I get the body of the stored procedure that I know is using the view, but I'm having trouble getting the actual procedure name.

推荐答案

您只有一个选择.

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

syscomments 和 INFORMATION_SCHEMA.routines 有 nvarchar(4000) 列.因此，如果在位置 3998 处使用myViewName"，则不会找到它.syscomments 确实有多行，但 ROUTINES 会被截断.

syscomments and INFORMATION_SCHEMA.routines have nvarchar(4000) columns. So if "myViewName" is used at position 3998, it won't be found. syscomments does have multiple lines but ROUTINES truncates.

这篇关于查找要查看的所有参考文献的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！