一个统计表每天的新增行数及新增存储空间的功能
作者:whsnow 时间:2024-01-13 22:27:40
使用文中提供的代码做一个统计表每天的新增行数及新增存储空间的功能
实现步骤如下:
1. 创建表
创建表,存储每天的表空间占用情况
CREATE TABLE [dbo].[t_rpt_table_spaceinfo](
[table_name] [sysname] NOT NULL,
[record_date] [date] NOT NULL,
[record_time] [time](7) NOT NULL,
[rows_count] [bigint] NULL,
[reserved] [bigint] NULL,
[data_size] [bigint] NULL,
[index_size] [bigint] NULL,
[unused] [bigint] NULL,
CONSTRAINT [PK_t_rpt_table_spaceinfo] PRIMARY KEY CLUSTERED
(
[table_name] ASC,
[record_date] ASC,
[record_time] ASC
)
)
2. 新建作业
新建作业,作业计划每天凌晨运行一次,每天记录表占用的空间情况,存储到上一步建立的表中
作业中执行的T-SQL代码为:
SET NOCOUNT ON
/*创建临时表,存放用户表的空间及数据行数信息*/
CREATE TABLE #tablespaceinfo
(
nameinfo VARCHAR(500) ,
rowsinfo BIGINT ,
reserved VARCHAR(20) ,
datainfo VARCHAR(20) ,
index_size VARCHAR(20) ,
unused VARCHAR(20)
)
DECLARE @tablename VARCHAR(255);
/*使用游标,循环得到表空间使用情况*/
DECLARE Info_cursor CURSOR
FOR
SELECT '[' + [name] + ']'
FROM sys.tables
WHERE type = 'U';
OPEN Info_cursor
FETCH NEXT FROM Info_cursor INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #tablespaceinfo
EXEC sp_spaceused @tablename
FETCH NEXT FROM Info_cursor
INTO @tablename
END
INSERT INTO t_rpt_table_spaceinfo
(record_date, record_time, [table_name], [rows_count]
, reserved, [data_size], index_size, unused)
SELECT convert(date,getdate()), convert(varchar(8),getdate(),114), nameinfo, rowsinfo
,CAST(REPLACE(reserved, 'KB', '') AS BIGINT) ,CAST(REPLACE(datainfo, 'KB', '') AS BIGINT)
,CAST(REPLACE(index_size, 'KB', '') AS BIGINT) ,CAST(REPLACE(unused, 'KB', '') AS BIGINT)
FROM #tablespaceinfo
CLOSE Info_cursor
DEALLOCATE Info_cursor
DROP TABLE [#tablespaceinfo]
3. 查询结果
连续的数据记录之间做比较,即可得到数据的增量变化情况
示例代码如下:
;with table_spaceinfo as
(
select record_date, record_time, table_name, rows_count, reserved, data_size, index_size, unused
,ROW_NUMBER() over(PARTITION by table_name order by record_date,record_time asc) as list_no
from t_rpt_table_spaceinfo
)
select _a.table_name as 表名,convert(varchar(20),_a.record_date)+' '+convert(varchar(8),_a.record_time)+'~~'
+convert(varchar(20),_b.record_date)+' '+convert(varchar(8),_b.record_time) as [时间段范围]
,_b.rows_count-_a.rows_count as [新增的行数]
,_b.data_size - _a.data_size as [新增数据空间(KB)]
from table_spaceinfo _a
join table_spaceinfo _b on _a.table_name=_b.table_name and _a.list_no=_b.list_no-1
order by [时间段范围]
标签:统计表,行数,存储空间
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Python编程技巧连接列表的八种操作方法
2022-02-10 02:08:54
Python3实现的回文数判断及罗马数字转整数算法示例
2023-07-07 05:10:06
Linux(Ubuntu)下mysql5.7.17安装配置方法图文教程
2024-01-25 21:28:32
简单form标准化实例——整体布局
2007-05-11 17:04:00
![](https://img.aspxhome.com/file/uploadpic/20075/200751117421929.gif)
ubuntu下简单配置mysql数据库
2009-07-31 09:17:00
MySQL8自增主键变化图文详解
2024-01-25 19:08:38
![](https://img.aspxhome.com/file/2023/6/71786_0s.png)
又为ajax上传工具重新写了一个服务器端上传程序
2009-12-21 14:30:00
Python Socket编程详细介绍
2021-01-02 02:56:21
js购物车实现思路及代码(个人感觉不错)
2024-04-18 10:59:06
推荐19个很有用的 JavaScript 库
2011-05-14 16:33:00
详解Python列表赋值复制深拷贝及5种浅拷贝
2022-07-16 16:22:41
SQL学习笔记四 聚合函数、排序方法
2011-09-30 11:33:29
golang字符串本质与原理详解
2024-04-28 09:16:19
框架和框架之间的关系
2008-01-17 18:54:00
![](https://img.aspxhome.com/file/UploadPic/20081/17/2008117185848873s.jpg)
php中pcntl_fork创建子进程的方法实例
2023-11-15 02:28:21
![](https://img.aspxhome.com/file/2023/4/92824_0s.png)
Python数据可视化Pyecharts库的使用教程
2023-01-24 23:17:34
![](https://img.aspxhome.com/file/2023/0/101320_0s.png)
Python使用socket实现组播与发送二进制数据
2021-05-09 13:44:28
![](https://img.aspxhome.com/file/2023/9/65949_0s.png)
解析ROC曲线绘制(python+sklearn+多分类)
2021-04-06 12:16:16
![](https://img.aspxhome.com/file/2023/4/78564_0s.png)
Win下PyInstaller 安装和使用教程
2022-08-14 21:29:53
![](https://img.aspxhome.com/file/2023/1/94151_0s.png)
如何使用Git优雅的回滚实现
2022-03-17 15:49:15
![](https://img.aspxhome.com/file/2023/7/123727_0s.png)