SQLSERVER+ORACLE+MYSQL(MARIADB)自定义查询表名、列（字段）名、数据类型、表备注、列备注等

SQLSERVER

SELECT   表名 =
           CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END,
         索引 =
           J.NAME,
         表数据行数 =
           O.HS,
         表说明 =
           CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '') ELSE '' END,
         字段序号 =
           A.COLORDER,
         字段名 =
           A.NAME,
         标识 =
           CASE WHEN COLUMNPROPERTY(A.ID, A.NAME, 'IsIdentity') = 1 THEN '√' ELSE '' END,
         主键 =
           CASE
             WHEN EXISTS
                    (SELECT 1
                     FROM   SYSOBJECTS
                     WHERE      XTYPE = 'PK'
                            AND PARENT_OBJ = A.ID
                            AND NAME IN (SELECT NAME
                                         FROM   SYSINDEXES
                                         WHERE  INDID IN (SELECT INDID
                                                          FROM   SYSINDEXKEYS
                                                          WHERE      ID = A.ID
                                                                 AND COLID = A.COLID)))
             THEN
               '√'
             ELSE
               ''
           END,
         类型 =
           B.NAME,
         占用字节数 =
           A.LENGTH,
         长度 =
           COLUMNPROPERTY(A.ID, A.NAME, 'PRECISION'),
         小数位数 =
           ISNULL(COLUMNPROPERTY(A.ID, A.NAME, 'Scale'), 0),
         允许空 =
           CASE WHEN A.ISNULLABLE = 1 THEN '√' ELSE '' END,
         默认值 =
           ISNULL(E.TEXT, ''),
         字段说明 =
           ISNULL(G.[value], '')
FROM     SYSCOLUMNS A
         LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE
         INNER JOIN SYSOBJECTS D
           ON     A.ID = D.ID
              AND D.XTYPE = 'U'
              AND D.NAME <> 'dtproperties'
         LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT = E.ID
         LEFT JOIN SYS.EXTENDED_PROPERTIES G
           ON     A.ID = G.MAJOR_ID
              AND A.COLID = G.MINOR_ID
         LEFT JOIN SYS.EXTENDED_PROPERTIES F
           ON     D.ID = F.MAJOR_ID
              AND F.MINOR_ID = 0
         LEFT JOIN (SELECT A.NAME, B.ROWS AS HS
                    FROM   SYSOBJECTS A INNER JOIN SYSINDEXES B ON A.ID = B.ID
                    WHERE      A.TYPE = 'u'
                           AND B.INDID IN (0, 1)) O
           ON (D.NAME = O.NAME)
         LEFT JOIN SYS.OBJECTS AS H ON D.NAME = H.NAME
         LEFT JOIN SYS.INDEXES AS J ON H.OBJECT_ID = J.OBJECT_ID
WHERE    H.TYPE <> 's'
ORDER BY A.ID, A.COLORDER

 ORACLE

SELECT   D.TABLE_NAME TBNAME, COALESCE(T.COMMENTS, ' ') TBDESC,
         A.COLUMN_NAME COLUMNNAME, A.DATA_TYPE COLUMNTYPE,
         A.DATA_LENGTH WIDTH, A.DATA_SCALE PRECISION,
         D.NUM_ROWS, DECODE(A.NULLABLE, 'Y', '0', '1') NOTNULL,
         COALESCE(M.COMMENTS, ' ') COMMENTS, DECODE(K.UNIQUENESS, 'UNIQUE', '1', '0') UNIQUES,
         COALESCE(K.INDEX_NAME, ' ') INDEXNAME, DECODE(K.KEY, 'Y', '1', '0') MASTERKEY
FROM     USER_TAB_COLUMNS A
         INNER JOIN USER_TABLES D ON A.TABLE_NAME = D.TABLE_NAME
         LEFT JOIN USER_TAB_COMMENTS T ON T.TABLE_NAME = D.TABLE_NAME
         LEFT JOIN USER_COL_COMMENTS M
           ON     M.COLUMN_NAME = A.COLUMN_NAME
              AND M.TABLE_NAME = D.TABLE_NAME
         LEFT JOIN (SELECT E.INDEX_NAME, U.TABLE_NAME, U.COLUMN_NAME, E.UNIQUENESS, DECODE(P.CONSTRAINT_NAME, NULL, 'N', 'Y') KEY
                    FROM   USER_INDEXES E
                           INNER JOIN USER_IND_COLUMNS U ON E.INDEX_NAME = U.INDEX_NAME
                           LEFT JOIN (SELECT CONSTRAINT_NAME
                                      FROM   USER_CONSTRAINTS
                                      WHERE  CONSTRAINT_TYPE = 'P') P
                             ON E.INDEX_NAME = P.CONSTRAINT_NAME) K
           ON     K.TABLE_NAME = A.TABLE_NAME
              AND K.COLUMN_NAME = A.COLUMN_NAME
ORDER BY TBNAME

 

MYSQL/MARIADB

SELECT   (CASE ORDINAL_POSITION WHEN 1 THEN TABLE_NAME ELSE '' END) '库名', ORDINAL_POSITION AS '列的排列顺序',
         COLUMN_COMMENT AS '注释', COLUMN_NAME AS '列名',
         IS_NULLABLE AS '是否为空', DATA_TYPE AS '数据类型',
         CHARACTER_MAXIMUM_LENGTH AS '字符最大长度', COLUMN_DEFAULT AS '默认值',
         NUMERIC_PRECISION AS '数值精度(最大位数)', NUMERIC_SCALE AS '小数精度',
         COLUMN_TYPE AS 列类型, COLUMN_KEY 'KEY',
         EXTRA AS '额外说明'
FROM     INFORMATION_SCHEMA. `COLUMNS`
WHERE    TABLE_SCHEMA = :tableSchema
ORDER BY TABLE_NAME, ORDINAL_POSITION;