参考sql2012存储过程写的统计所有用户表尺寸大小的示例
时间:2024-01-25 05:50:18
可以结合sp_MSforeachdb再遍历所有用户数据库查看所有表的尺寸大小,注意它的参数@sql不能超过nvarchar(2000),这里就不贴出代码了。
另外还可以定期运行并将结果保存下来,以便观察数据变化趋势。
查询单个数据库的所有用户表尺寸大小:
Select @@servername as ServerName,db_name() as DBName ,object_id as ObjectID, schema_name(schema_id) as SchName, name as TableName
,Rowcnt as Rows,Columns,Indexes,RowLength
,ReservedKb, TableUsedKb
,UsedKb-TableUsedKb as IndexUsedKb,ReservedKb-UsedKb as UnusedKb
,create_date as CreateDate,modify_date as LastModifiedDate, getutcdate() as TrackingUTCTime
From
(select
object_id
,schema_id
,name
,(Select max(row_count) from sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id and p.index_id < 2) as Rowcnt
,(Select Count(1) from dbo.syscolumns with(nolock) where id = t.object_id) as Columns
,(Select Count(distinct index_id) from sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id) as Indexes
,(SELECT SUM(length) FROM dbo.syscolumns with(nolock) WHERE id = t.object_id) as RowLength
,IsNull((Select SUM(reserved_page_count) from sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id),0)*8
+ IsNull((Select sum(reserved_page_count)
FROM sys.dm_db_partition_stats p2 with(nolock)
inner join sys.internal_tables it with(nolock) on p2.object_id = it.object_id
WHERE it.parent_id = t.object_id
AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 as ReservedKb
,IsNull((Select SUM(in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
from sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id and p.index_id < 2),0)* 8 as TableUsedKb
,IsNull((Select SUM(used_page_count) from sys.dm_db_partition_stats p with(nolock) where p.object_id=t.object_id),0)*8
+ IsNull((Select sum(used_page_count)
FROM sys.dm_db_partition_stats p2 with(nolock)
inner join sys.internal_tables it with(nolock) on p2.object_id = it.object_id
WHERE it.parent_id = t.object_id
AND it.internal_type IN (202,204,207,211,212,213,214,215,216,221,222,236)),0)* 8 as UsedKb
,create_date
,modify_date
from sys.tables t with(nolock)
where Type='U'
) A
order by ReservedKb desc
标签:sql2012,用户表尺寸
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Keras中的多分类损失函数用法categorical_crossentropy
2023-06-23 12:25:37
![](https://img.aspxhome.com/file/2023/5/63935_0s.jpg)
python使用xlrd和xlwt读写Excel文件的实例代码
2021-08-30 08:43:55
opencv 图像轮廓的实现示例
2023-07-21 15:37:17
![](https://img.aspxhome.com/file/2023/5/115505_0s.jpg)
js实现鼠标感应向下滑动隐藏菜单的方法
2024-05-02 17:23:21
Java用正则表达式实现${name}形式的字符串模板实例
2023-08-05 14:10:01
Python读取csv文件实例解析
2023-01-21 07:47:32
![](https://img.aspxhome.com/file/2023/8/68408_0s.png)
python保存图片的四个常用方法
2023-10-03 15:06:37
![](https://img.aspxhome.com/file/2023/6/68936_0s.jpg)
golang redigo发布订阅使用的方法
2024-04-29 13:05:23
![](https://img.aspxhome.com/file/2023/5/133925_0s.png)
mysql-8.0.15-winx64 解压版安装教程及退出的三种方式
2024-01-23 21:56:20
![](https://img.aspxhome.com/file/2023/4/67584_0s.png)
微信小程序仿朋友圈发布动态功能
2024-04-17 10:01:01
![](https://img.aspxhome.com/file/2023/6/136426_0s.png)
纯JS单页面赛车游戏制作代码分享
2024-02-23 14:10:17
![](https://img.aspxhome.com/file/2023/2/56512_0s.jpg)
Python基础入门之魔法方法与异常处理
2021-07-01 07:29:39
Django初步使用Celery处理耗时任务和定时任务问题
2023-10-15 07:41:59
![](https://img.aspxhome.com/file/2023/0/88010_0s.png)
解决项目pycharm能运行,在终端却无法运行的问题
2021-11-03 21:05:07
Python获取当前公网ip并自动断开宽带连接实例代码
2021-08-28 12:40:27
在Python的web框架中编写创建日志的程序的教程
2021-11-25 05:14:07
![](https://img.aspxhome.com/file/2023/4/67244_0s.png)
Python用20行代码实现完整邮件功能
2023-04-06 12:20:49
![](https://img.aspxhome.com/file/2023/6/105756_0s.gif)
js实现多张图片打包成zip
2024-04-22 22:15:09
Python中dict和set的用法讲解
2023-12-09 05:20:55
![](https://img.aspxhome.com/file/2023/7/81507_0s.png)
python 定时器,实现每天凌晨3点执行的方法
2023-06-25 12:32:01