达梦数据库常用SQL之查询有大字段的表及表中存在的大字段类型

接上篇文章

[post cid=”313”/]

由于只查询表名可能还不满足实际需求，因此将表中存在的大字段也一并列出来，其中重复的大字段做了去重，具体SQL如下：

SELECT
        TABLE_NAME,
        LISTAGG(DATA_TYPE, ',') WITHIN GROUP (
ORDER BY
        TABLE_NAME)
FROM
        (
                SELECT DISTINCT
                        B.NAME    AS TABLE_NAME,
                        D."TYPE$" AS DATA_TYPE
                FROM
                        SYSOBJECTS B
                INNER JOIN SYSCOLUMNS D
                ON
                        B.ID=D.ID
                WHERE
                        B.TYPE$   ='SCHOBJ'
                    AND B.SUBTYPE$='UTAB'
                    AND D.TYPE$  IN ('CLOB', 'BLOB', 'TEXT', 'IMAGE', 'LONGVARBINARY', 'LONGVARCHAR')
                    AND B.SCHID   =
                        (
                                SELECT A.ID FROM SYSOBJECTS A WHERE A.NAME='SYSDBA' AND A.TYPE$='SCH'
                        )
                GROUP BY
                        B.NAME,
                        D."TYPE$"
        )
GROUP BY
        TABLE_NAME;