我有各种数据库,可以确定的是我正在删除一些真正孤立的东西(在这种情况下是一个视图).要使用的 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.
更多推荐
查找要查看的所有参考文献
发布评论