【O】单据转换、业务流程、反写规则表数据分析

--单据转换、业务流程、反写规则
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('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;