db2查询字段备注_SQL基础7：SQLSERVER、ORACLE、DB2中SQL语句查询表字段名、注释、字段类型...

一、ORACLE

查某个架构下的所有表注释

select

A.OWNER 架构名,

A.TABLE_NAME 表名,

B.COMMENTS 表解释,

A.COLUMN_NAME 字段名,

A.COMMENTS 字段解释

from dba_col_comments A

LEFT JOIN dba_tab_comments B ON A.TABLE_NAME=B.TABLE_NAME AND B.OWNER='架构名' AND B.TABLE_TYPE='TABLE'

WHERE A.OWNER='TMAIN'

二、SQLSERVER

SELECT

sch.NAME 架构名,

tab.name 表名,

ISNULL(ep2.value, '') 表解释,

col.colorder 序号,

col.name 列名,

ISNULL(ep.value, '') 列说明,

ind.rows 表数据量,

t.name 数据类型,

col.length 长度,

ISNULL(COLUMNPROPERTY(col.id, col.name, 'Scale'), 0) AS 小数位数,

CASE WHEN COLUMNPROPERTY(col.id, col.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,

CASE WHEN EXISTS( SELECT 1 FROM dbo.sysindexes si

INNER JOIN dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid

INNER JOIN dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid

INNER JOIN dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'

WHERE sc.id = col.id AND sc.colid = col.colid) THEN '√' ELSE ''END AS 主键,

CASE WHEN col.isnullable = 1 THEN '√' ELSE ''END AS 允许空,

ISNULL(comm.text, '') AS 默认值

FROM dbo.syscolumns col

LEFT JOIN dbo.systypes t ON col.xtype = t.xusertype

INNER JOIN dbo.sysobjects obj ON col.id = obj.id AND obj.xtype = 'U' AND obj.status >= 0

LEFT JOIN sysindexes ind ON obj.id=ind.id AND ind.indid IN(0,1) AND obj.Type='U'

LEFT JOIN sys.tables tab ON obj.ID=tab.object_id

LEFT join sys.schemas sch on tab.schema_id = sch.schema_id

LEFT JOIN dbo.syscomments comm ON col.cdefault = comm.id

LEFT JOIN sys.extended_properties ep ON col.id = ep.major_id AND col.colid = ep.minor_id AND ep.name = 'MS_Description'

LEFT JOIN sys.extended_properties ep2 ON tab.object_id = ep2.major_id AND ep2.minor_id = 0

三、DB2

SELECT a.TABSCHEMA,a.TABNAME,a.REMARKS,

b.COLNAME,b.REMARKS

FROM syscat.tables a

LEFT JOIN syscat.COLUMNS b on a.TABSCHEMA=b.TABSCHEMA and a.TABNAME=b.TABNAME and a.TYPE='T'

WHERE A.TABSCHEMA='架构名'