[SQL]查找tablespace下所有的表

SELECT DISTINCT A.TABSCHEMA, A.TABNAME, A.TYPE, A.TBSPACE, A.INDEX_TBSPACE, A.LONG_TBSPACE, A.DATACAPTURE, A.DEFINER, A.REMARKS, A.CARD, A.STATS_TIME  FROM SYSCAT.TABLES A, TABLE ( SELECT COUNT(*) AS NUM_COLUMNS FROM SYSCAT.DATAPARTITIONEXPRESSION S WHERE A.TABNAME = S.TABNAME AND A.TABSCHEMA = S.TABSCHEMA) AS PARTITION_COLUMNS WHERE (A.TYPE='T' AND ((PARTITION_COLUMNS.NUM_COLUMNS > 0 AND 'USERSPACE1' IN  (SELECT C.TBSPACE FROM SYSCAT.DATAPARTITIONS B, SYSIBM.SYSTABLESPACES C WHERE B.TBSPACEID = C.TBSPACEID AND A.TABNAME = B.TABNAME AND A.TABSCHEMA = B.TABSCHEMA UNION  SELECT C.TBSPACE FROM SYSCAT.DATAPARTITIONS B, SYSIBM.SYSTABLESPACES C WHERE B.LONG_TBSPACEID = C.TBSPACEID AND A.TABNAME = B.TABNAME AND A.TABSCHEMA = B.TABSCHEMA))OR ( A.TBSPACE='USERSPACE1'OR A.INDEX_TBSPACE='USERSPACE1'OR A.LONG_TBSPACE='USERSPACE1' ) )) FOR FETCH ONLY;

SELECT DISTINCT A.TABSCHEMA, A.TABNAME, A.TYPE, A.TBSPACE, A.INDEX_TBSPACE, A.LONG_TBSPACE, A.DATACAPTURE, A.DEFINER, A.REMARKS, A.CARD, A.STATS_TIME FROM SYSCAT.TABLES A, TABLE ( SELECT COUNT(*) AS NUM_COLUMNS FROM SYSCAT.DATAPARTITIONEXPRESSION S WHERE A.TABNAME = S.TABNAME AND A.TABSCHEMA = S.TABSCHEMA) AS PARTITION_COLUMNS WHERE (A.TYPE='T' AND ((PARTITION_COLUMNS.NUM_COLUMNS > 0 AND 'USERSPACE1' IN (SELECT C.TBSPACE FROM SYSCAT.DATAPARTITIONS B, SYSIBM.SYSTABLESPACES C WHERE B.TBSPACEID = C.TBSPACEID AND A.TABNAME = B.TABNAME AND A.TABSCHEMA = B.TABSCHEMA UNION SELECT C.TBSPACE FROM SYSCAT.DATAPARTITIONS B, SYSIBM.SYSTABLESPACES C WHERE B.LONG_TBSPACEID = C.TBSPACEID AND A.TABNAME = B.TABNAME AND A.TABSCHEMA = B.TABSCHEMA))OR ( A.TBSPACE='USERSPACE1'OR A.INDEX_TBSPACE='USERSPACE1'OR A.LONG_TBSPACE='USERSPACE1' ) )) FOR FETCH ONLY;

SELECT owner,TABLE_NAME,tablespace_name FROM all_tables WHERE tablespace_name = 'USERS';

select owner,table_name,tablespace_name from all_tables where tablespace_name = 'USERS';