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