金蝶cloud常用数据库表

一、数据库查询常用表

--查询数据表
select * from
(
select convert(varchar(4000),t1.FKERNELXML.query(&＃39;//TableName&＃39;)) as &＃39;Item&＃39;,t1.FKERNELXML&＃xff0c;t2.FNAME, t1.*
from T_META_OBJECTTYPE t1
left join T_META_OBJECTTYPE_L t2 on t1.FID&＃61;t2.FID
--where t1.fid&＃61;&＃39;SAL_SaleOrder&＃39;
) t
where Item<>&＃39;&＃39; and FNAME like &＃39;%收料通知单%&＃39;

 通过表T_META_OBJECTTYPE的FKERNNELXML字段进行xml查找

 库存状态列表

select t1.FSTOCKSTATUSID,t1.FNUMBER,t2.FNAME from T_BD_STOCKSTATUS t1
left join  T_BD_STOCKSTATUS_L t2 on t1.FSTOCKSTATUSID&＃61;t2.FSTOCKSTATUSID

单据类型

select t1.FBILLTYPEID,t1.FNUMBER,t2.FNAME from T_BAS_BILLTYPE t1
left join T_BAS_BILLTYPE_L t2 on t1.FBILLTYPEID&＃61;t2.FBILLTYPEID
 where t1.FBILLFORMID&＃61;&＃39;QM_InspectBill&＃39;  --单据类型--检验单

基础资料

T_ORG_ORGANIZATIONS  组织表

T_ORG_ORGANIZATIONS_L   组织表

T_BAS_ASSISTANTDATA_L 辅助资料分类

T_BAS_ASSISTANTDATAENTRY  辅助资料列表

T_BAS_ASSISTANTDATAENTRY_L  辅助资料列表

t_bd_accountbook_l&＃xff08;账簿多语言&＃xff09;

t_bd_accountbook&＃xff08;账簿&＃xff09;

t_bd_material(物料)

t_bd_material_l(物料多语言)

T_BD_OPERATOR(业务员) 
T_BD_OPERATOR_L(业务员多语言)

T_BD_STAFF(员工表)

t_BD_Stock(仓库)

t_bd_supplier(供应商)

 财务

t_AP_payable(应付单表头) 
T_AP_PAYBILL(付款单) 
t_AR_receivable(应收单表头) 
t_AR_receivable(应收单表头) 
t_gl_voucher(凭证) 
T_IV_PURCHASEIC(采购发票) 
T_IV_SALESIC(销售发票)

供应链

T_PUR_POORDER(采购订单) 
T_PUR_PRICELIST &＃xff08;价目表&＃xff09; 
T_PUR_RECEIVE(采购收料单) 
T_PUR_REQUISITION(采购申请) 
T_SAL_DELIVERYNOTICE(销售发货通知单)
T_SAL_ORDER(销售订单) 
T_SAL_OUTSTOCK(销售出库单) 
T_SAL_RETURNSTOCK(销售退货单) 
T_STK_INSTOCK(入库单) 
T_STK_InvBal(库存余额表) 
T_STK_INVENTORY(即时库存表)

 4.制造

T_ENG_BOM(物料清单) 
T_PRD_INSTOCK(生产入库单) 
T_PRD_MO(生产订单) 
T_PRD_MORPT(生产汇报单) 
T_PRD_PICKMTRL(生产领料单) 
T_PRD_PPBOM(生产用料清单) 
T_PRD_PREPAREMTRL(生产备料单据头) 
T_PRD_RETURNMTRL(生产退料单) 
T_SUB_FEEDMTRL(委外补料单) 
T_SUB_PICKMTRL(委外领料单) 
T_SUB_PPBOM(委外用料清单) 
T_SUB_REQORDER(委外订单) 
T_SUB_RETURNMTRL(委外退料单)

--物料名称
select m.fmaterialid,m.fmasterid, fname,m.fnumber from t_bd_material m join t_bd_material_l l on 
l.fmaterialid&＃61;m.fmaterialid
where 1&＃61;1
--and m.fmaterialid&＃61;147002
and m.fnumber&＃61;&＃39;XACPWM0067&＃39;
--and l.fname like &＃39;%扯面%&＃39;
order by m.fmaterialid desc 


--组织
select V_SCM_OWNERORG_L.FNAME,* from V_SCM_OWNERORG left join V_SCM_OWNERORG_L on 

V_SCM_OWNERORG.FORGID&＃61;V_SCM_OWNERORG_L.FORGID

select l.FNAME,* from T_ORG_ORGANIZATIONS o
join T_ORG_ORGANIZATIONS_L l
on l.FORGID&＃61;o.FORGID


--元数据
select * from t_meta_objecttype_l ml,t_meta_objecttype m where fname like &＃39;%工序汇报%&＃39; and ml.fid&＃61;m.fid


--部门
select b.FNAME,* from T_BD_DEPARTMENT a join T_BD_DEPARTMENT_L b on a.FDEPTID&＃61;b.FDEPTID


--单据转换
select * from T_META_CONVERTRULE where fid&＃61;&＃39;PlanOrder_PPBom&＃39;


--通过元数据唯一id查询菜单
select * from T_META_CONSOLEDETAIL where FOBJECTID like &＃39;%stk_%&＃39;


--反写规则
select rl.FNAME, * from T_BF_WRITEBACKRULE r
left join T_BF_WRITEBACKRULE_L rl on rl.FID&＃61;r.FID and rl.FLOCALEID&＃61;2052
left join T_BF_WRITEBACKRULECUST rc on r.FID&＃61;rc.FID
where (rl.FNAME like &＃39;%生产线生产%&＃39; or rl.FID like &＃39;%sfc_%&＃39;) and rc.FFORBIDSTATUS&＃61;&＃39;A&＃39;


--转换规则
select * from T_META_CONVERTRULE where FID like &＃39;%rem_%&＃39;

select * from T_META_CONVERTRULE_L


--反写规则
select * from T_BF_WRITEBACKRULE where fsourceformid like &＃39;%SFC_%&＃39; and ftargetformid like &＃39;%SFC_%&＃39;


--参数
select * from t_bas_sysparameter


--枚举值
select * from T_META_FORMENUM e
join T_META_FORMENUM_L el on el.FID&＃61;e.FID
where FNAME like &＃39;%生产线领料单%&＃39;


--单据类型
select * from T_BAS_BILLTYPE where FBILLFORMID like &＃39;%REM_INSTOCK%&＃39;


--菜单操作对应类名
select * from T_MDL_FORMOPERATIONTYPE


--操作里面的服务
select * from T_MDL_FORMBUSINESS_L where FDESC like &＃39;%更新即时库存%&＃39;
select * from T_MDL_FORMBUSINESS where FACTIONID&＃61;45


--保存界面布局
select * from T_BAS_FormParameter
where FPARAOBJID like &＃39;%SFC_OperationPlanning%&＃39;


--用户
select * from T_SEC_USER 


--用户参数
select * FROM T_BAS_UserParameter where FPARAMETEROBJID like &＃39;%rem_%&＃39;


--IDE函数
select fl.FNAME,f.FAPPEARANCECLASS,f.FELEMENTCLASS, * from T_MDL_ELEMENTTYPE f join T_MDL_ELEMENTTYPE_L fl on f.FID&＃61;fl.FID
where FELEMENTCLASS like &＃39;%OPERATIONSTATUS%&＃39; or FAPPEARANCECLASS like &＃39;%OPERATIONSTATUS%&＃39;


--枚举
select * from T_META_FORMENUM_L where fname &＃61;&＃39;REM_业务类型&＃39;
select * from t_Meta_Formenumitem where fid&＃61;&＃39;c4a9508f-7af8-4edb-bb2f-fc21c866ee41&＃39;


--权限对象&＃xff0c;权限项
select * from T_SEC_PERMISSIONOBJECT
select * from T_SEC_PERMISSIONOBJECTENTRY


--通过菜单查找元数据对象&＃xff0c;查找表名&＃xff08;在xml中搜t_&＃xff09;
select * from T_META_OBJECTTYPE h join T_META_OBJECTTYPE_L l on l.FID&＃61;h.FID where FNAME like &＃39;%运算日志%&＃39;


--单位
select l.FNAME, * from T_BD_UNIT h join T_BD_UNIT_L l on h.FUNITID&＃61;l.FUNITID where l.FNAME like &＃39;%千克%&＃39; 

--单据转换、业务流程、反写规则
SELECT * FROM T_BF_DEFVERSION
SELECT * FROM T_BF_DEFVERSION_L
SELECT * FROM T_BF_DEFVERSIONLOOKUP
SELECT * FROM T_BF_INSTANCE
SELECT * FROM T_BF_INSTANCEAMOUNT
SELECT * FROM T_BF_INSTANCEAMOUNTHIS
SELECT * FROM T_BF_INSTANCEENTRY
SELECT * FROM T_BF_INSTANCEENTRYHIS
SELECT * FROM T_BF_INSTANCEHIS
SELECT * FROM T_BF_INSTANCESNAP
SELECT * FROM T_BF_INSTANCESNAPHIS
SELECT * FROM T_BF_INSTANCETRACK
SELECT * FROM T_BF_MYFAVOURITE
SELECT * FROM T_BF_PROCDEF
SELECT * FROM T_BF_PROCDEF_L
SELECT * FROM T_BF_PROCESSTYPE
SELECT * FROM T_BF_PROCESSTYPE_L
SELECT * FROM T_BF_PUBLISH
SELECT * FROM T_BF_PUBLISH_L
SELECT * FROM T_BF_PUBLISHENTRY
SELECT * FROM T_BF_TABLEDEFINE
SELECT * FROM T_BF_TRACKBACKUPLOG
SELECT * FROM T_BF_WRITEBACKRULE
SELECT * FROM T_BF_WRITEBACKRULE_L
SELECT * FROM T_BF_WRITEBACKRULECUST
SELECT * FROM T_META_CONVERTRULE
SELECT * FROM T_META_CONVERTRULE_L
--SQL SERVER单据转换、业务流程、反写规则
CREATE TABLE TMP_COSTCALTABLE 
(FTABLENAME VARCHAR(30));

INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_DEFVERSION&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_DEFVERSION_L&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_DEFVERSIONLOOKUP&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCE&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCEAMOUNT&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCEAMOUNTHIS&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCEENTRY&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCEENTRYHIS&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCEHIS&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCESNAP&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCESNAPHIS&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_INSTANCETRACK&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_MYFAVOURITE&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_PROCDEF&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_PROCDEF_L&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_PROCESSTYPE&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_PROCESSTYPE_L&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_PUBLISH&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_PUBLISH_L&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_PUBLISHENTRY&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_TABLEDEFINE&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_TRACKBACKUPLOG&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_WRITEBACKRULE&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_WRITEBACKRULE_L&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_BF_WRITEBACKRULECUST&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_META_CONVERTRULE&＃39;);
INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES(&＃39;T_META_CONVERTRULE_L&＃39;
SELECT * FROM TMP_COSTCALTABLE
--查看系统所有表占用的空间情况
create table tmpspace (Fname varchar(50),
Frows int,
Freserved varchar(50),
Fdata varchar(50),
Findex_size varchar(50),
Funused varchar(50));
--插入所有表数据大小
insert into tmpspace (Fname,Frows,Freserved, Fdata,Findex_size,Funused) 
exec sp_msforeachTable &＃64;Command1&＃61;"sp_spaceused &＃39;?&＃39;
--查询
WITH FCBCOSTSPACE AS (
select CONVERT(DECIMAL,replace(sp.fdata,&＃39;KB&＃39;,&＃39;&＃39;))/1024 SPACE_MB,sp.* from tmpspace sp
inner join TMP_COSTCALTABLE cb on cb.FTABLENAME&＃61;sp.Fname)
SELECT * FROM FCBCOSTSPACE
ORDER BY SPACE_MB DESC
--临时表占用的总大小&＃xff08;M&＃xff09;
select SUM(CONVERT(DECIMAL,replace(sp.fdata,&＃39;KB&＃39;,&＃39;&＃39;)))/1024 M from tmpspace sp
inner join TMP_COSTCALTABLE cb on cb.FTABLENAME&＃61;sp.Fname;
--drop table tmpspace;
--DROP TABLE TMP_COSTCALTABLE;