如何查询ASE SYBASE或SQL SERVER中存储过程、SQL用到的表和列

以下方案分别覆盖SQL Server和ASE Sybase两种数据库，分为静态依赖查询（查询对象定义中写的所有表/列引用）和实际运行依赖查询（查询真实执行过的SQL用到的表/列，符合「系统真实使用」的需求）两种场景。

一、SQL Server 实现方法 #

1. 静态依赖查询（查询所有存储过程定义中用到的表和列） #

SELECT 
    DISTINCT
    o.name AS 引用存储过程名,
    COALESCE(referenced_schema_name, 'dbo') AS 表Schema,
    referenced_entity_name AS 表名,
    referenced_minor_name AS 列名
FROM 
    sys.objects o
CROSS APPLY 
    sys.dm_sql_referenced_entities (SCHEMA_NAME(o.schema_id) + '.' + o.name, 'OBJECT')
WHERE 
    o.type = 'P' -- 过滤只查询存储过程
    AND referenced_minor_id > 0 -- 过滤仅引用表未引用列的记录

2. 真实运行依赖查询（基于执行计划缓存，查询实际执行过的SQL用到的表和列） #

WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/2004/07/showplan')
SELECT 
    DISTINCT
    t.value('(@Schema)[1]', 'sysname') AS 表Schema,
    t.value('(@Table)[1]', 'sysname') AS 表名,
    t.value('(@Column)[1]', 'sysname') AS 列名
FROM 
    sys.dm_exec_cached_plans cp
CROSS APPLY 
    sys.dm_exec_query_plan(cp.plan_handle) qp
CROSS APPLY 
    query_plan.nodes('//ColumnReference') AS ref(t)
WHERE 
    t.exist('@Schema') = 1 
    AND t.exist('@Table') = 1 
    AND t.exist('@Column') = 1
    AND t.value('(@Schema)[1]', 'sysname') NOT IN ('sys', 'INFORMATION_SCHEMA') -- 过滤系统表

注意事项：

• 执行计划缓存会随数据库重启、缓存淘汰被清空，数据仅覆盖缓存保留周期内的执行记录
• 如果需要保留更长时间的执行历史，建议开启SQL Server自带的查询存储（Query Store）功能，查询逻辑可以基于查询存储的执行计划视图调整

二、ASE Sybase 实现方法 #

1. 静态依赖查询（查询所有存储过程定义中用到的表和列） #

SELECT DISTINCT
    so.name AS 存储过程名,
    user_name(so2.uid) AS 表Schema,
    so2.name AS 表名,
    sc.name AS 列名
FROM 
    syscomments scm
JOIN 
    sysobjects so ON scm.id = so.id
JOIN 
    sysobjects so2 ON patindex('%' + so2.name + '%', scm.text) > 0
JOIN 
    syscolumns sc ON so2.id = sc.id AND patindex('%' + sc.name + '%', scm.text) > 0
WHERE 
    so.type = 'P' -- 过滤只查询存储过程
    AND so2.type = 'U' -- 过滤只查询用户表

2. 真实运行依赖查询 #

ASE Sybase可以通过开启语句级审计或者监控服务器（Monitoring Server）捕获实际执行的SQL，解析后提取用到的表和列。也可以用下面的语句查询当前正在运行的SQL涉及的对象：

SELECT 
    DB_NAME(dbid) AS 数据库名,
    object_name(id, dbid) AS 表名,
    sql_text AS 执行的SQL语句
FROM 
    master..sysprocesses
CROSS APPLY 
    master..monProcessSQLText()
WHERE 
    id != 0

注意事项：如果需要获取长期的真实使用记录，建议提前开启ASE的审计功能，持久化保存所有执行的SQL语句后再做批量解析。

内容的提问来源于stack exchange，提问作者RGS