查找要查看的所有参考文献

本文介绍了查找要查看的所有参考文献的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有各种数据库，可以确定的是我正在删除一些真正孤立的东西(在这种情况下是一个视图).要使用的 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.