 |
| |
[03/09/2010]
DB2: How do I tell what objects reference a table?
By: Mark Jacobsen
This 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;
===================
Mark Jacobsen: I hope you have found this article or tip helpful. If so, please consider making a donation. 100% of all proceeds will be directed to charity: water.
Contact me at MarkJacobsen.net or @MarkJacobsen
|
|
 |
Thanks for stopping by. Please be sure to visit some of my sites for your communication needs: Toll Free Virtual PBX, Real Estate Call Capture, Hosted PBX, and Virtual Fax.
All News Articles...
|
|
