T-SQL:循环显示与表相关的存储过程 [英] T-SQL: Show stored procedures related to tables, cyclically
问题描述
我正在使用以下 t-sql 代码:
I'm using the following t-sql code:
USE [my_database]
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%table_name%'
为了显示使用表table_name
的所有存储过程.
in order to show all the Stored Procedures that use the table table_name
.
我想为我数据库中的所有表做这项工作.
I want do this work for all tables in my database.
如何执行此任务并组织输出?
How can I perform this task and organize the output?
推荐答案
这对表和存储过程都使用信息架构.您可以更改或摆脱 ROUTINE_TYPE 条件来添加函数,您可以更改表类型以返回视图.
This uses information schema for both tables, and stored procedures. You can change or get rid of ROUTINE_TYPE condition to add functions, and you can change table type to return views.
此答案通过检查存储过程所依赖的表来生成其结果.我认为这将是一个更准确的结果,然后检查名称是否在查询文本中.如果该过程引用了注释部分中的表,则该结果将不会在第一个查询中返回,而是在第二个和其他给出的答案中返回.
This answer produces its results by checking what tables a stored procedure depends on. I think this will be a much more accurate result then checking if a name is in the query text. If the procedure refers to a table in a comment section, then this result will not be returned in the first query, but will be in the second and other answers given.
SELECT t.TABLE_NAME, s.ROUTINE_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.ROUTINES s ON
s.ROUTINE_NAME IN (SELECT referencing_entity_name
FROM sys.dm_sql_referencing_entities(TABLE_SCHEMA + '.' + TABLE_NAME, 'OBJECT'))
AND s.ROUTINE_TYPE = 'PROCEDURE'
WHERE t.TABLE_TYPE = 'BASE TABLE'
edit:这里是如何在没有函数的情况下获取依赖项.(我最喜欢这个方法)
edit: Here's how to get the dependencies without the function. (I like this method the best)
SELECT DISTINCT t.name [TableName], p.name [ProcedureName]
FROM sys.objects t
LEFT JOIN sys.sql_dependencies d ON
d.referenced_major_id = t.object_id
LEFT JOIN sys.objects p ON
p.object_id = d.object_id
AND p.type = 'p'
WHERE t.type = 'u'
如果您的具体用途只是查找与表名匹配的任何字符串,则以下方法可行:
If your specific use is to just find any string that matches a table name, below will work:
SELECT t.TABLE_NAME, s.ROUTINE_NAME
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.ROUTINES s
ON CHARINDEX(t.TABLE_NAME, s.ROUTINE_DEFINITION) > 0
AND s.ROUTINE_TYPE = 'PROCEDURE'
WHERE t.TABLE_TYPE = 'BASE TABLE'
这篇关于T-SQL:循环显示与表相关的存储过程的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!