AI 助理
备案控制台
文档
输入文档关键字查找
首页 云数据库 OceanBase 版 操作指南 数据传输 附录 查询表对象 SQL

查询表对象 SQL

更新时间：
产品详情
相关技术圈
我的收藏

您在创建数据迁移或数据同步任务时，指定需要迁移或同步的对象后，数据传输会自动过滤不支持的表，可能会导致识别出的表数量和实际需要迁移或同步的表数量不一致，您可以通过本文的 SQL 语句进行查询。

查询表的基本信息

不同数据源类型查询表基本信息的 SQL 语句如下。

  • MySQL 数据源

    SELECT
    NULL TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    TABLE_COLLATION,
    ENGINE
    FROM
    information_schema.tables
    WHERE
    TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA NOT IN(
        'mysql',
        'information_schema',
        'performance_schema'
    )
    AND TABLE_SCHEMA IN ()
    AND TABLE_NAME IN ();

  • OceanBase 数据库 MySQL 租户数据源

    SELECT
    /*+ query_timeout(600000000)*/
    NULL TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS,
    TABLE_COLLATION,
    ENGINE
    FROM
    information_schema.tables
    WHERE
    TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA NOT IN(
        'mysql',
        'information_schema',
        'performance_schema'
    )
    AND BINARY TABLE_SCHEMA IN ()
    AND BINARY TABLE_NAME IN ();

  • OceanBase 数据库 Oracle 租户数据源

    SELECT
    /*+ query_timeout(600000000)*/
    NULL,
    A.OWNER,
    A.TABLE_NAME,
    A.NUM_ROWS,
    A.PARTITIONED,
    A.IOT_TYPE
FROM
    ALL_TABLES A
WHERE
    A.TABLE_NAME IN (
        SELECT
            OBJECT_NAME
        FROM
            ALL_OBJECTS
        WHERE
            UPPER(OBJECT_TYPE) = 'TABLE'
    )
    AND A.OWNER NOT IN ('SYSTEM', 'SYS')
    AND A.TEMPORARY = 'N'
    AND A.OWNER IN ()
    AND A.TABLE_NAME IN ();

  • Oracle 数据源

    SELECT
    NULL,
    A.OWNER,
    A.TABLE_NAME,
    A.NUM_ROWS,
    A.PARTITIONED,
    A.IOT_TYPE
FROM
    ALL_TABLES A
WHERE
    (A.OWNER, A.TABLE_NAME) NOT IN (
        SELECT
            OWNER,
            MVIEW_NAME
        FROM
            ALL_MVIEWS
        UNION
        ALL
        SELECT
            LOG_OWNER,
            LOG_TABLE
        FROM
            ALL_MVIEW_LOGS
    )
    AND A.OWNER NOT IN ('SYSTEM', 'SYS')
    AND A.TEMPORARY = 'N'
    AND A.OWNER IN ()
    AND A.TABLE_NAME IN ();

  • PostgreSQL 数据源

    SELECT
    TABLE_CATALOG,
    TABLE_SCHEMA,
    TABLE_NAME
    FROM
    information_schema.tables
    WHERE
    TABLE_TYPE = 'BASE TABLE'
    AND TABLE_SCHEMA <> 'pg_catalog'
    AND TABLE_SCHEMA <> 'information_schema'
    AND TABLE_SCHEMA IN ()
    AND TABLE_NAME IN ();

过滤外表

数据传输会默认过滤掉外表的迁移/同步，不同数据源类型过滤外表的 SQL 语句如下。

  • Oracle 数据源

    SELECT NULL,A.OWNER,A.TABLE_NAME,A.NUM_ROWS,A.PARTITIONED,A.IOT_TYPE
        FROM ALL_TABLES A
        WHERE (A.OWNER,A.TABLE_NAME) NOT IN ( SELECT OWNER,MVIEW_NAME FROM ALL_MVIEWS UNION ALL SELECT LOG_OWNER,LOG_TABLE FROM ALL_MVIEW_LOGS)
        AND A.OWNER NOT IN ('SYSTEM','SYS') AND A.TEMPORARY='N' AND A.EXTERNAL != 'YES'
        %s

  • OceanBase 数据库 Oracle 租户

    SELECT /*HINT*/ NULL,A.OWNER,A.TABLE_NAME,A.NUM_ROWS,A.PARTITIONED,A.IOT_TYPE FROM ALL_TABLES A
        WHERE A.TABLE_NAME IN (
        SELECT OBJECT_NAME FROM ALL_OBJECTS WHERE UPPER(OBJECT_TYPE)='TABLE'
        ) AND A.OWNER NOT IN ('SYSTEM','SYS') AND A.TEMPORARY='N' AND A.EXTERNAL != 'YES'
        %s

查询视图

不同数据源类型查询视图的 SQL 语句如下。

  • MySQL 数据源

    SELECT
    TABLE_NAME,
    TABLE_SCHEMA,
    VIEW_DEFINITION
    FROM
    information_schema.views
    WHERE
    TABLE_SCHEMA IN ();

  • OceanBase 数据库 MySQL 租户数据源

    SELECT
    TABLE_NAME,
    TABLE_SCHEMA,
    VIEW_DEFINITION
FROM
    information_schema.views
WHERE
    TABLE_SCHEMA IN ();

  • OceanBase 数据库 Oracle 租户数据源

    SELECT
    VIEW_NAME,
    OWNER
FROM
    ALL_VIEWS
WHERE
    OWNER IN ()
ORDER BY
    VIEW_NAME;

  • Oracle 数据源

    SELECT
    VIEW_NAME,
    OWNER
FROM
    ALL_VIEWS
WHERE
    OWNER IN ()
ORDER BY
    VIEW_NAME;

查询物化视图

Oracle 数据源查询物化视图的 SQL 语句如下。

SELECT
    MVIEW_NAME AS NAME,
    OWNER
FROM
    ALL_MVIEWS
WHERE
    OWNER IN ()
UNION
SELECT
    LOG_TABLE AS NAME,
    LOG_OWNER
FROM
    ALL_MVIEW_LOGS
WHERE
    LOG_OWNER IN ();

查询有主键的表

不同数据源类型查询有主键的表的 SQL 语句如下。

  • MySQL 数据源

    SELECT
    DISTINCT TABLE_NAME,
    TABLE_SCHEMA
FROM
    information_schema.statistics
WHERE
    TABLE_SCHEMA IN ()
    AND upper(INDEX_NAME) = 'PRIMARY';

  • OceanBase 数据源 MySQL 租户

    SELECT
    DISTINCT TABLE_NAME,
    TABLE_SCHEMA
FROM
    information_schema.statistics
WHERE
    TABLE_SCHEMA IN ()
    AND upper(INDEX_NAME) = 'PRIMARY';

  • Oracle 数据源

    SELECT
    TABLE_NAME,
    OWNER
FROM
    ALL_CONSTRAINTS
WHERE
    OWNER IN ()
    AND STATUS = 'ENABLED'
    AND VALIDATED = 'VALIDATED'
    AND CONSTRAINT_TYPE = 'P';

查询有主键或非空唯一键的表

不同数据源类型查询有主键或非空唯一键的表的 SQL 语句如下。

  • MySQL 数据源

    SELECT
    DISTINCT TABLE_NAME,
    TABLE_SCHEMA
FROM
    information_schema.statistics
WHERE
    TABLE_SCHEMA IN ()
GROUP BY
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME
HAVING
    count(*) = count(
        IF(
            upper(nullable) != 'YES'
            AND NON_UNIQUE = 0,
            1,
            NULL
        )
    );

  • OceanBase 数据库 MySQL 租户数据源

    SELECT
    DISTINCT TABLE_NAME,
    TABLE_SCHEMA
FROM
    information_schema.statistics
WHERE
    TABLE_SCHEMA IN ()
GROUP BY
    TABLE_SCHEMA,
    TABLE_NAME,
    INDEX_NAME
HAVING
    count(*) = count(
        IF(
            upper(nullable) != 'YES'
            and NON_UNIQUE = 0,
            1,
            NULL
        )
    );

  • Oracle 数据源

    SELECT
    TABLE_NAME,
    OWNER
FROM
    ALL_CONSTRAINTS
WHERE
    OWNER IN ()
    AND STATUS = 'ENABLED'
    AND VALIDATED = 'VALIDATED'
    AND CONSTRAINT_TYPE = 'P';

  • OceanBase 数据库 Oracle 租户数据源

    SELECT
    /*+ query_timeout(600000000)*/
    DISTINCT TABLE_NAME,
    OWNER
FROM
    (
        SELECT
            DC.OWNER,
            DC.TABLE_NAME,
            DC.CONSTRAINT_NAME
        FROM
            ALL_CONS_COLUMNS DCC
            JOIN ALL_CONSTRAINTS DC ON DCC.CONSTRAINT_NAME = DC.CONSTRAINT_NAME
            AND DCC.OWNER = DC.OWNER
            JOIN ALL_TAB_COLUMNS DTC ON DCC.COLUMN_NAME = DTC.COLUMN_NAME
            AND DCC.OWNER = DTC.OWNER
            AND DCC.TABLE_NAME = DTC.TABLE_NAME
        WHERE
            DCC.OWNER IN ()
            AND DC.CONSTRAINT_TYPE IN ('U', 'P')
        GROUP BY
            DC.OWNER,
            DC.TABLE_NAME,
            DC.CONSTRAINT_NAME
        HAVING
            COUNT(*) = COUNT(
                CASE
                    DTC.NULLABLE
                    WHEN 'Y' THEN NULL
                    ELSE 1
                END
            )
        MINUS
        SELECT
            TABLE_OWNER,
            TABLE_NAME,
            INDEX_NAME
        FROM
            ALL_IND_EXPRESSIONS
        WHERE
            TABLE_OWNER IN ()
    );

查询有函数 UK 的表

不同数据源类型查询有函数 UK 的表的 SQL 语句如下。

  • Oracle 数据源

    SELECT
    DISTINCT A.TABLE_NAME,
    A.TABLE_OWNER
FROM
    ALL_IND_EXPRESSIONS A
    JOIN ALL_INDEXES B ON A.TABLE_OWNER = B.TABLE_OWNER
    AND A.TABLE_NAME = B.TABLE_NAME
    AND A.INDEX_NAME = B.INDEX_NAME
WHERE
    A.TABLE_OWNER IN ()
    AND B.UNIQUENESS = 'UNIQUE';

  • OceanBase 数据库 Oracle 租户数据源

    SELECT
    /*+ query_timeout(600000000)*/
    DISTINCT A.TABLE_NAME,
    A.TABLE_OWNER
FROM
    ALL_IND_EXPRESSIONS A
    JOIN ALL_INDEXES B ON A.TABLE_OWNER = B.TABLE_OWNER
    AND A.TABLE_NAME = B.TABLE_NAME
    AND A.INDEX_NAME = B.INDEX_NAME
WHERE
    A.TABLE_OWNER IN ()
    AND B.UNIQUENESS = 'UNIQUE';

查询是否存在某些数据类型的表

不同数据源查询是否存在某些数据类型的表的 SQL 语句如下。

  • Oracle 数据源

    SELECT
    DISTINCT TABLE_NAME AS TABLENAME,
    OWNER AS DBNAME
FROM
    all_tab_columns
WHERE
    OWNER IN ()
    AND DATA_TYPE IN / NOT IN ();

  • MySQL 数据源

    SELECT
    DISTINCT TABLE_NAME AS TABLENAME,
    TABLE_SCHEMA
FROM
    information_schema.COLUMNS
WHERE
    TABLE_SCHEMA IN ()
    AND DATA_TYPE IN / NOT IN ();

查询有主键且包含某些数据类型的表

不同数据源类型查询有主键且包含某些数据类型的表的 SQL 语句如下。

  • OceanBase 数据库 MySQL 租户数据源

    SELECT
    DISTINCT TABLE_NAME,
    TABLE_SCHEMA
FROM
    information_schema.COLUMNS
WHERE
    UPPER(COLUMN_KEY) = 'PRI'
    AND TABLE_SCHEMA IN ()
    AND UPPER(COLUMN_TYPE) IN ();

  • MySQL 数据源

    SELECT
    DISTINCT TABLE_NAME,
    TABLE_SCHEMA
FROM
    information_schema.COLUMNS
WHERE
    UPPER(COLUMN_KEY) = 'PRI'
    AND TABLE_SCHEMA IN ()
    AND UPPER(COLUMN_TYPE) IN ();

查询包含 LOB 类型的表

不同数据源类型查询包含 LOB 类型的表的 SQL 语句如下。

  • Oracle 数据源

    SELECT
    DISTINCT TABLE_NAME,
    OWNER
FROM
    ALL_LOBS
WHERE
    OWNER IN ();

  • OceanBase 数据库 Oracle 租户数据源

    SELECT
    /*+ query_timeout(600000000)*/
    DISTINCT TABLE_NAME,
    OWNER
FROM
    ALL_TAB_COLUMNS
WHERE
    DATA_TYPE IN ('BLOB', 'CLOB')
    AND OWNER IN ();

查询 Oracle 数据库中 row_movement 为 DISABLED 的表

SELECT
    TABLE_NAME,
    OWNER
FROM
    ALL_TABLES
WHERE
    OWNER IN ()
    AND ROW_MOVEMENT = 'DISABLED';

查询 OceanBase 数据库中包含 pseudo 列的表

  • OceanBase 数据库 MySQL 租户

    SELECT
    DISTINCT TABLE_NAME,
    TABLE_SCHEMA
FROM
    information_schema.columns
WHERE
    TABLE_SCHEMA IN ()
    AND COLUMN_NAME in (
        'OMS_PK_INCRMT',
        'OMS_OBJECT_NUMBER',
        'OMS_RELATIVE_FNO',
        'OMS_BLOCK_NUMBER',
        'OMS_ROW_NUMBER'
    );

  • OceanBase 数据库 Oracle 租户

    SELECT
    DISTINCT TABLE_NAME,
    OWNER
FROM
    ALL_TAB_COLUMNS
WHERE
    OWNER IN ()
    AND COLUMN_NAME IN (
        'OMS_PK_INCRMT',
        'OMS_OBJECT_NUMBER',
        'OMS_RELATIVE_FNO',
        'OMS_BLOCK_NUMBER',
        'OMS_ROW_NUMBER'
    );

查询列数量超过指定值的表

不同数据源类型查询列数量超过指定值的表的 SQL 语句如下。

  • Oracle 数据源

    SELECT
    TABLE_NAME,
    OWNER
FROM
    ALL_TAB_COLUMNS
WHERE
    OWNER = ?
GROUP BY
    TABLE_NAME,
    OWNER
HAVING COUNT(1) > ?;

  • OceanBase 数据库 Oracle 租户数据源

    SELECT
    /*+ query_timeout(600000000)*/
    TABLE_NAME,
    OWNER
FROM
    ALL_TAB_COLUMNS
WHERE
    OWNER = ?
GROUP BY
    TABLE_NAME,
    OWNER
HAVING COUNT(1) > ?;
上一篇：修改 RDS PostgreSQL 实例的日志级别下一篇：附录
文档推荐