Snowflake中删除DIM_SALES_DETAILS表前的对象依赖查询方法

以下是针对主流数据库系统的查询方案，帮你找出所有依赖该表的视图、物化视图、存储过程、任务等对象：

Snowflake #

Snowflake提供了INFORMATION_SCHEMA下的系统视图来查询依赖关系，同时单独的任务视图可以排查任务依赖：

视图与物化视图 #

SELECT 
    TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE
FROM 
    INFORMATION_SCHEMA.TABLES
WHERE 
    TABLE_TYPE IN ('VIEW', 'MATERIALIZED VIEW')
    AND TABLE_NAME IN (
        SELECT REFERENCED_TABLE_NAME
        FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE
        WHERE TABLE_NAME = 'DIM_SALES_DETAILS'
    );

存储过程与函数 #

SELECT 
    ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE
FROM 
    INFORMATION_SCHEMA.ROUTINES
WHERE 
    ROUTINE_DEFINITION LIKE '%DIM_SALES_DETAILS%';

任务 #

SELECT 
    DATABASE_NAME, SCHEMA_NAME, NAME, DEFINITION
FROM 
    TABLE(INFORMATION_SCHEMA.TASKS())
WHERE 
    DEFINITION LIKE '%DIM_SALES_DETAILS%';

Oracle #

Oracle通过dba_dependencies视图管理对象依赖，任务则需要查询调度器相关表：

视图与物化视图 #

SELECT 
    owner, object_name, object_type
FROM 
    dba_dependencies
WHERE 
    referenced_owner = '表所属用户' -- 替换为实际表所有者
    AND referenced_name = 'DIM_SALES_DETAILS'
    AND object_type IN ('VIEW', 'MATERIALIZED VIEW');

存储过程、函数与包 #

SELECT 
    owner, object_name, object_type
FROM 
    dba_dependencies
WHERE 
    referenced_owner = '表所属用户' -- 替换为实际表所有者
    AND referenced_name = 'DIM_SALES_DETAILS'
    AND object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'PACKAGE BODY');

DBMS_SCHEDULER任务 #

SELECT 
    owner, job_name
FROM 
    dba_scheduler_jobs
WHERE 
    job_action LIKE '%DIM_SALES_DETAILS%';

SQL Server #

SQL Server通过系统视图sys.sql_expression_dependencies追踪对象依赖，作业则查询msdb库的相关表：

视图与索引视图（物化视图） #

SELECT 
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS object_name,
    o.type_desc AS object_type
FROM 
    sys.objects o
JOIN 
    sys.sql_expression_dependencies sed ON o.object_id = sed.referencing_id
WHERE 
    sed.referenced_id = OBJECT_ID('DIM_SALES_DETAILS')
    AND o.type IN ('V');

存储过程与函数 #

SELECT 
    SCHEMA_NAME(o.schema_id) AS schema_name,
    o.name AS object_name,
    o.type_desc AS object_type
FROM 
    sys.objects o
JOIN 
    sys.sql_expression_dependencies sed ON o.object_id = sed.referencing_id
WHERE 
    sed.referenced_id = OBJECT_ID('DIM_SALES_DETAILS')
    AND o.type IN ('P', 'FN', 'IF', 'TF');

SQL Server Agent作业 #

SELECT 
    j.name AS job_name
FROM 
    msdb.dbo.sysjobs j
JOIN 
    msdb.dbo.sysjobsteps js ON j.job_id = js.job_id
WHERE 
    js.command LIKE '%DIM_SALES_DETAILS%';

注意事项 #

• 所有查询需确保你拥有足够权限（比如Oracle的DBA_*视图需要DBA权限，Snowflake需ACCOUNTADMIN或类似权限）；
• 使用LIKE模糊匹配的查询可能出现误判，建议结合业务逻辑验证结果；
• 替换查询中标记的占位符（如表所属用户）为实际值。

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