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='架构名'