按数据库系统表列出金蝶表及字段描述
按数据库系统表列出金蝶表及字段描述(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 表名