--用户表 usr_sz_tms_dev1 usr_tms_test1
SELECT *
FROM (SELECT T.OWNER, T.TABLE_NAME, N.TABLE_NAME EMPTY_TYPE
FROM ALL_TABLES T
LEFT JOIN ALL_TABLES N
ON (T.TABLE_NAME = N.TABLE_NAME AND
N.OWNER = UPPER('USR_TMS_TEST1'))
WHERE T.OWNER = UPPER('USR_SZ_TMS_DEV1')
UNION ALL
SELECT T.OWNER, T.TABLE_NAME, N.TABLE_NAME EMPTY_TYPE
FROM ALL_TABLES T
LEFT JOIN ALL_TABLES N
ON (T.TABLE_NAME = N.TABLE_NAME AND
N.OWNER = UPPER('USR_SZ_TMS_DEV1'))
WHERE T.OWNER = UPPER('USR_TMS_TEST1'))
WHERE EMPTY_TYPE IS NULL;
--字段
SELECT *
FROM (SELECT T.OWNER,
T.TABLE_NAME,
T.COLUMN_NAME,
N.COLUMN_NAME EMPTY_TYPE
FROM ALL_TAB_COLUMNS T
LEFT JOIN ALL_TAB_COLUMNS N
ON (T.TABLE_NAME = N.TABLE_NAME AND
T.COLUMN_NAME = N.COLUMN_NAME AND
N.OWNER = UPPER('USR_TMS_TEST1'))
WHERE T.OWNER = UPPER('USR_SZ_TMS_DEV1')
UNION ALL
SELECT T.OWNER,
T.TABLE_NAME,
T.COLUMN_NAME,
N.COLUMN_NAME EMPTY_TYPE
FROM ALL_TAB_COLUMNS T
LEFT JOIN ALL_TAB_COLUMNS N
ON (T.TABLE_NAME = N.TABLE_NAME AND
T.COLUMN_NAME = N.COLUMN_NAME AND
N.OWNER = UPPER('USR_SZ_TMS_DEV1'))
WHERE T.OWNER = UPPER('USR_TMS_TEST1'))
WHERE EMPTY_TYPE IS NULL;
--字段类型
SELECT *
FROM (SELECT T.OWNER,
T.TABLE_NAME,
T.COLUMN_NAME,
T.DATA_TYPE,
N.DATA_TYPE EMPTY_TYPE
FROM ALL_TAB_COLUMNS T
LEFT JOIN ALL_TAB_COLUMNS N
ON (T.TABLE_NAME = N.TABLE_NAME AND
T.COLUMN_NAME = N.COLUMN_NAME AND T.DATA_TYPE = N.DATA_TYPE AND
N.OWNER = UPPER('USR_TMS_TEST1'))
WHERE T.OWNER = UPPER('USR_SZ_TMS_DEV1')
UNION ALL
SELECT T.OWNER,
T.TABLE_NAME,
T.COLUMN_NAME,
T.DATA_TYPE,
N.DATA_TYPE EMPTY_TYPE
FROM ALL_TAB_COLUMNS T
LEFT JOIN ALL_TAB_COLUMNS N
ON (T.TABLE_NAME = N.TABLE_NAME AND
T.COLUMN_NAME = N.COLUMN_NAME AND T.DATA_TYPE = N.DATA_TYPE AND
N.OWNER = UPPER('USR_SZ_TMS_DEV1'))
WHERE T.OWNER = UPPER('USR_TMS_TEST1'))
WHERE EMPTY_TYPE IS NULL;