查询数据表的字段信息

查询数据表的字段信息

1.Oracle数据库

--COL_NAME字段名，COL_TYPE字段类型，COL_COMMENT字段含义；
--tableName表名，userName用户名；
SELECT
	C.COLUMN_NAME COL_NAME,
	C.DATA_TYPE COL_TYPE,
	( SELECT COMMENTS FROM ALL_COL_COMMENTS CM WHERE CM.TABLE_NAME = C.TABLE_NAME AND CM.OWNER = C.OWNER AND CM.COLUMN_NAME = C.COLUMN_NAME AND ROWNUM = 1 ) COL_COMMENT 
FROM
	ALL_TAB_COLUMNS C 
WHERE
	C.TABLE_NAME = 'tableName' 
	AND C.OWNER = 'userName'

2.MySQL数据库

--COL_NAME字段名，COL_TYPE字段类型，COL_COMMENT字段含义；
--tableName表名，userName用户名；
SELECT
	COLUMN_NAME COL_NAME,
	DATA_TYPE COL_TYPE,
	COLUMN_COMMENT COL_COMMENT 
FROM
	information_schema.COLUMNS 
WHERE
	TABLE_NAME ='tableName' 
	AND TABLE_SCHEMA ='userName'

3.达梦6数据库

--COL_NAME字段名，COL_TYPE字段类型，COL_COMMENT字段含义；
--tableName表名，userName用户模式名；
SELECT 
        COLS.NAME COL_NAME,
        COLS.TYPE COL_TYPE,
        COLS.RESVD5 COL_COMMENT 
FROM 
        SYSDBA.SYSTABLES T 
INNER JOIN 
        SYSDBA.SYSCOLUMNS COLS 
ON 
        COLS.ID=T.ID 
INNER JOIN 
        SYSDBA.SYSSCHEMAS SCH 
ON 
        SCH.SCHID=T.SCHID 
WHERE 
        T.NAME='tableName' 
    AND SCH.NAME  ='userName'

4.达梦7数据库

--COL_NAME字段名，COL_TYPE字段类型，COL_COMMENT字段含义，COL_LENGTH字段长度；
--tableName表名，userName用户模式名；
SELECT
        COLS.NAME COL_NAME     ,
        COLS.TYPE$ COL_TYPE    ,
        CC.COMMENT$ COL_COMMENT,
        COLS.LENGTH$ COL_LENGTH
FROM
        SYSOBJECTS T
INNER JOIN SYSCOLUMNS COLS
ON
        COLS.ID=T.ID
LEFT JOIN SYSCOLUMNCOMMENTS CC
ON
        CC.TVNAME =T.NAME
    AND COLS.NAME =CC.COLNAME
    AND CC.SCHNAME='userName'
LEFT JOIN SYSTABLECOMMENTS TC
ON
        TC.TVNAME    =T.NAME
    AND TC.TABLE_TYPE='TABLE'
    AND TC.SCHNAME   ='userName'
WHERE
        T.NAME ='tableName'
    AND T.SCHID=
        (
                SELECT ID FROM SYSOBJECTS WHERE TYPE$='SCH' AND NAME='userName'
        )

5.欢迎拓展