按数据库系统表列出金蝶表及字段描述

按数据库系统表列出金蝶表及字段描述

按数据库系统表列出金蝶表及字段描述(Test By v12.2)

SELECT 

obj_name AS '表名',字段名,字段类型,字段长度,

子系统名称,表描述,表详细描述,

字段描述,字段详细描述

FROM

(

SELECT

sys.sysobjects.[name] AS 'obj_name',

sys.sysobjects.[id] AS 'obj_id',

sys.sysobjects.xtype AS 'obj_xtype',

sys.syscolumns.[name] AS '字段名',

sys.systypes.[name] AS '字段类型',

sys.syscolumns.[length] AS '字段长度'

FROM sys.syscolumns

LEFT JOIN sys.sysobjects ON sys.syscolumns.id = sys.sysobjects.id

LEFT JOIN sys.systypes ON sys.syscolumns.xtype = sys.systypes.xtype

WHERE 1=1

AND sys.sysobjects.xtype IN('U','V')

--AND sys.sysobjects.[name] IN('t_ICItem','t_Item','t_ItemCustom')

) t_db

LEFT JOIN 

(

SELECT 

--

t_s.FSubSysID,

t_s.FName AS '子系统名称',

--

--t_t.FSubSystemID,

t_t.FTableID,

t_t.FTableName,

t_t.FDescription AS '表描述',

t_t.FTableNote AS '表详细描述',

t_t.FType AS '表Type',

--t_t.FSefDefSign,

--

--t_f.FTableID,

t_f.FFieldName,

t_f.FFieldType,

t_f.FDescription AS '字段描述',

t_f.FFieldNote AS '字段详细描述',

t_f.FType AS '字段Type'

--t_f.FSefDefSign

FROM t_FieldDescription t_f

LEFT JOIN t_TableDescription t_t ON t_f.FTableID=t_t.FTableID

LEFT JOIN t_subsystem t_s ON t_t.FSubSystemID=t_s.FSubSysID

--ORDER BY t_s.FSubSysID,t_t.FTableID

) t_k3 ON t_db.obj_name=t_k3.FTableName AND t_db.字段名=t_k3.FFieldName

WHERE 1=1

--AND 表描述 LIKE '%发票%'

AND 字段描述 LIKE '%洲别%'

ORDER by 表名