如何获取存储过程中的表列表? [英] How can I get the list of tables in the stored procedure?

问题描述

db 中有很多表和 sp.我找到了在特定 sp(存储过程)中使用的表名.

There are lot of tables and sp in the db. I find the tables name which are used in the specific sp (stored procedure).

sp_depends %sp_name% 没有给出想要的结果.我也用过INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.ROUTINES 表.

sp_depends %sp_name% not give the desire result. I am also used INFORMATION_SCHEMA.TABLES,INFORMATION_SCHEMA.ROUTINES tables.

但结果没有完全满足我的要求.

But the result is not full fill my requirment.

推荐答案

得票最高的两个答案使用了大量应避免使用的弃用表格.
这是一种更简洁的方法.

The two highest voted answers use a lot of deprecated tables that should be avoided.
Here's a much cleaner way to do it.

SELECT DISTINCT p.name AS proc_name, t.name AS table_name
FROM sys.sql_dependencies d 
INNER JOIN sys.procedures p ON p.object_id = d.object_id
INNER JOIN sys.tables     t ON t.object_id = d.referenced_major_id
ORDER BY proc_name, table_name

适用于 MS SQL SERVER 2005+

• sysdepends 应替换为sys.sql_dependencies
  • 新表使用 object_id 而不是 id
  • 新表使用 referenced_major_id 而不是 depid
  • sysdepends should be replaced with sys.sql_dependencies
    • The new table uses object_id instead of id
    • The new table uses referenced_major_id instead of depid
    • 正如marc_s 指出的，请改用sys.tables 和 sys.procedures
    • 注意:这可以防止必须检查 o.xtype = 'p'(等)
    • As marc_s pointed out, instead use sys.tables and sys.procedures
    • Note: This prevents having to check where o.xtype = 'p' (etc.)

    此外，实际上不需要使用 ROW_NUMBER() 只是 以确保我们只返回每个记录集之一.这就是 DISTINCT 的用途！

    Also, there is really no need for a CTE which uses ROW_NUMBER() just in order to make sure we only have one of each record set returned. That's what DISTINCT is there for!

    • 事实上，SQL 足够聪明，可以在幕后使用 DISTINCT.

    • 我提交证据:附件 A.以下查询具有相同的执行计划！

    -- Complex
    WITH MyPeople AS (
      SELECT id, name,
      ROW_NUMBER() OVER(PARTITION BY id, name ORDER BY id, name) AS row
      FROM People)
    SELECT id, name
    FROM MyPeople
    WHERE row = 1
    
    -- Better
    SELECT DISTINCT id, name
    FROM People
    

  这篇关于如何获取存储过程中的表列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！