二、K3 Cloud 开发插件《K3 Cloud 常用数据表整理》 一、数据库查询常用表 复制代码 --查询数据表 select * from ( select convert(varchar(4000),t1.FKERNELXML.query('//TableName')) as 'Item',t1.FKERNELXML，t2.FNAME, t1.* from T_META_OBJECTTYPE t1 left join T_META_OBJECTTYPE_L t2 on t1.FID=t2.FID --where t1.fid='SAL_SaleOrder' ) t where Item<>'' and FNAME like '%收料通知单%' 复制代码 通过表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=t2.FSTOCKSTATUSID 单据类型 select t1.FBILLTYPEID,t1.FNUMBER,t2.FNAME from T_BAS_BILLTYPE t1 left join T_BAS_BILLTYPE_L t2 on t1.FBILLTYPEID=t2.FBILLTYPEID where t1.FBILLFORMID='QM_InspectBill' --单据类型--检验单 基础资料 T_ORG_ORGANIZATIONS 组织表 T_ORG_ORGANIZATIONS_L 组织表 T_BAS_ASSISTANTDATA_L 辅助资料分类 T_BAS_ASSISTANTDATAENTRY 辅助资料列表 T_BAS_ASSISTANTDATAENTRY_L 辅助资料列表 t_bd_accountbook_l（账簿多语言） t_bd_accountbook（账簿） 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 （价目表） 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=m.fmaterialid where 1=1 --and m.fmaterialid=147002 and m.fnumber='XACPWM0067' --and l.fname like '%扯面%' 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=V_SCM_OWNERORG_L.FORGID select l.FNAME,* from T_ORG_ORGANIZATIONS o join T_ORG_ORGANIZATIONS_L l on l.FORGID=o.FORGID --元数据 select * from t_meta_objecttype_l ml,t_meta_objecttype m where fname like '%工序汇报%' and ml.fid=m.fid --部门 select b.FNAME,* from T_BD_DEPARTMENT a join T_BD_DEPARTMENT_L b on a.FDEPTID=b.FDEPTID --单据转换 select * from T_META_CONVERTRULE where fid='PlanOrder_PPBom' --通过元数据唯一id查询菜单 select * from T_META_CONSOLEDETAIL where FOBJECTID like '%stk_%' --反写规则 select rl.FNAME, * from T_BF_WRITEBACKRULE r left join T_BF_WRITEBACKRULE_L rl on rl.FID=r.FID and rl.FLOCALEID=2052 left join T_BF_WRITEBACKRULECUST rc on r.FID=rc.FID where (rl.FNAME like '%生产线生产%' or rl.FID like '%sfc_%') and rc.FFORBIDSTATUS='A' --转换规则 select * from T_META_CONVERTRULE where FID like '%rem_%' select * from T_META_CONVERTRULE_L --反写规则 select * from T_BF_WRITEBACKRULE where fsourceformid like '%SFC_%' and ftargetformid like '%SFC_%' --参数 select * from t_bas_sysparameter --枚举值 select * from T_META_FORMENUM e join T_META_FORMENUM_L el on el.FID=e.FID where FNAME like '%生产线领料单%' --单据类型 select * from T_BAS_BILLTYPE where FBILLFORMID like '%REM_INSTOCK%' --菜单操作对应类名 select * from T_MDL_FORMOPERATIONTYPE --操作里面的服务 select * from T_MDL_FORMBUSINESS_L where FDESC like '%更新即时库存%' select * from T_MDL_FORMBUSINESS where FACTIONID=45 --保存界面布局 select * from T_BAS_FormParameter where FPARAOBJID like '%SFC_OperationPlanning%' --用户 select * from T_SEC_USER --用户参数 select * FROM T_BAS_UserParameter where FPARAMETEROBJID like '%rem_%' --IDE函数 select fl.FNAME,f.FAPPEARANCECLASS,f.FELEMENTCLASS, * from T_MDL_ELEMENTTYPE f join T_MDL_ELEMENTTYPE_L fl on f.FID=fl.FID where FELEMENTCLASS like '%OPERATIONSTATUS%' or FAPPEARANCECLASS like '%OPERATIONSTATUS%' --枚举 select * from T_META_FORMENUM_L where fname ='REM_业务类型' select * from t_Meta_Formenumitem where fid='c4a9508f-7af8-4edb-bb2f-fc21c866ee41' --权限对象，权限项 select * from T_SEC_PERMISSIONOBJECT select * from T_SEC_PERMISSIONOBJECTENTRY --通过菜单查找元数据对象，查找表名（在xml中搜t_） select * from T_META_OBJECTTYPE h join T_META_OBJECTTYPE_L l on l.FID=h.FID where FNAME like '%运算日志%' --单位 select l.FNAME, * from T_BD_UNIT h join T_BD_UNIT_L l on h.FUNITID=l.FUNITID where l.FNAME like '%千克%' --单据转换、业务流程、反写规则 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));</P> <P>INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_DEFVERSION'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_DEFVERSION_L'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_DEFVERSIONLOOKUP'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCE'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEAMOUNT'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEAMOUNTHIS'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEENTRY'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEENTRYHIS'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCEHIS'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCESNAP'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCESNAPHIS'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_INSTANCETRACK'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_MYFAVOURITE'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCDEF'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCDEF_L'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCESSTYPE'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PROCESSTYPE_L'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PUBLISH'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PUBLISH_L'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_PUBLISHENTRY'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_TABLEDEFINE'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_TRACKBACKUPLOG'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_WRITEBACKRULE'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_WRITEBACKRULE_L'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_BF_WRITEBACKRULECUST'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_META_CONVERTRULE'); INSERT INTO TMP_COSTCALTABLE(FTABLENAME) VALUES('T_META_CONVERTRULE_L' 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 @Command1="sp_spaceused '?' --查询 WITH FCBCOSTSPACE AS ( select CONVERT(DECIMAL,replace(sp.fdata,'KB',''))/1024 SPACE_MB,sp.* from tmpspace sp inner join TMP_COSTCALTABLE cb on cb.FTABLENAME=sp.Fname) SELECT * FROM FCBCOSTSPACE ORDER BY SPACE_MB DESC --临时表占用的总大小（M） select SUM(CONVERT(DECIMAL,replace(sp.fdata,'KB','')))/1024 M from tmpspace sp inner join TMP_COSTCALTABLE cb on cb.FTABLENAME=sp.Fname; --drop table tmpspace; --DROP TABLE TMP_COSTCALTABLE;</P>