How to Tell What Objects Reference a DB2 Table

This “little” 🙂 query will do it for you. Just change the where clause at the bottom…

SELECT Type_X AS "Type" 
, RefObj_X AS "Referencing Object" 
FROM ( 
SELECT RTRIM(SYSPROC.PROCSCHEMA)||'.'||RTRIM(SYSPROC.PROCNAME) AS RefObj_X 
, RTRIM(SYSPROC.PROCSCHEMA) AS Schema_X 
, RTRIM(SYSPROC.PROCNAME) AS Obj_X 
, RTRIM(PDEP.BSCHEMA)||'.'||RTRIM(PDEP.BNAME) AS BaseObj_X 
, 'Proc' AS Type_X 
FROM SYSCAT.PACKAGEDEP PDEP 
INNER JOIN SYSIBM.sysdependencies SYSDEP 
ON SYSDEP.BSCHEMA = PDEP.BSCHEMA 
AND SYSDEP.BNAME = PDEP.PKGNAME 
INNER JOIN SYSIBM.sysprocedures SYSPROC 
ON SYSDEP.DNAME = SYSPROC.SPECIFICNAME 

UNION ALL 

SELECT RTRIM(TRIGSCHEMA)||'.'||RTRIM(TRIGNAME) AS RefObj_X 
, RTRIM(TRIGSCHEMA) AS Schema_X 
, RTRIM(TRIGNAME) AS Obj_X 
, RTRIM(BSCHEMA)||'.'||RTRIM(BNAME) AS BaseObj_X 
, 'Trigger' AS Type_X 
FROM SYSCAT.TRIGDEP 

UNION ALL 

SELECT RTRIM(VIEWSCHEMA)||'.'||RTRIM(CAST((VIEWNAME) AS CHAR(126))) AS RefObj_X 
, RTRIM(VIEWSCHEMA) AS Schema_X 
, RTRIM(CAST((VIEWNAME) AS CHAR(126))) AS Obj_X 
, RTRIM(BSCHEMA)||'.'||RTRIM(BNAME) AS BaseObj_X 
, CASE DTYPE WHEN 'S' 
THEN 'SUMMARY TABLE' 
ELSE 'View' 
END AS Type_X 
FROM SYSCAT.VIEWDEP 

UNION ALL 

SELECT RTRIM(INDSCHEMA)||'.'||RTRIM(INDNAME) AS RefObj_X 
, RTRIM(INDSCHEMA) AS Schema_X 
, RTRIM(INDNAME) AS Obj_X 
, RTRIM(TABSCHEMA)||'.'||RTRIM(TABNAME) AS BaseObj_X 
, 'Index' AS Type_X 
FROM SYSCAT.INDEXES 

UNION ALL 

SELECT RTRIM(TABSCHEMA)||'.'||RTRIM(TABNAME) AS RefObj_X 
, RTRIM(TABSCHEMA) AS Schema_X 
, RTRIM(TABNAME) AS Obj_X 
, RTRIM(BASE_TABSCHEMA)||'.'||RTRIM(BASE_TABNAME) AS BaseObj_X 
, 'Alias' AS Type_X 
FROM SYSCAT.TABLES 
WHERE TYPE = 'A' 

UNION ALL 

SELECT DISTINCT RTRIM(CAST(RTRIM(TABSCHEMA) AS VARCHAR(126))) || '.' || RTRIM(TABNAME) AS RefObj_X 
, RTRIM(CAST(RTRIM(TABSCHEMA) AS VARCHAR(126))) AS Schema_X 
, RTRIM(TABNAME) AS Obj_X 
, RTRIM(REFTABSCHEMA)||'.'||RTRIM(REFTABNAME) AS BaseObj_X 
, 'Table' AS Type_X 
FROM SYSCAT.REFERENCES 
) AS RefTbl 
WHERE BaseObj_X = 'XX.MY_TABLE' 
ORDER BY 
Type_X 
, RefObj_X;

Leave a Reply