MSSQL数据库每个表占用的空间、大小
时间：2021-07-01 10:21:17
帮助过：3人阅读
if NOT EXISTS
2 (
SELECT *
3 FROM dbo.sysobjects
4 WHERE id
= object_id(N
‘[dbo].[tablespaceinfo]‘)
5 AND OBJECTPROPERTY(id, N
‘IsUserTable‘)
= 1)
create table tablespaceinfo
--创建结果存储表 (nameinfo varchar(50) , rowsinfo int , reserved varchar(20) , datainfo varchar(20) , index_size varchar(20) , unused varchar(20) ) delete
6 FROM tablespaceinfo
--清空数据表 declare @tablename varchar(255) --表名称 declare @cmdsql varchar(500) DECLARE Info_cursor CURSOR FORSELECT o.name
7 FROM dbo.sysobjects o
8 WHERE OBJECTPROPERTY(o.id, N
‘IsTable‘)
= 1
9 AND o.name
NOT LIKE N
‘#%%‘
10 ORDER BY o.name
OPEN Info_cursor
FETCH NEXT
11 FROM Info_cursor
INTO @tablename WHILE @@FETCH_STATUS = 0 BEGIN if EXISTS
12 (
SELECT *
13 FROM dbo.sysobjects
14 WHERE id
= object_id(
@tablename)
15 AND OBJECTPROPERTY(id, N
‘IsUserTable‘)
= 1)
execute sp_executesql N
‘insert into tablespaceinfo exec sp_spaceused @tbname‘, N
‘@tbname varchar(255)‘,
@tbname = @tablename FETCH NEXT
16 FROM Info_cursor
INTO @tablename
17 END CLOSE Info_cursor
DEALLOCATE Info_cursor
GO --itlearner注：显示数据库信息 sp_spaceused @updateusage = ‘TRUE‘ --itlearner注：显示表信息SELECT *
18 FROM tablespaceinfo
19 ORDER BY cast(
left(
ltrim(
rtrim(reserved)) ,
len(
ltrim(
rtrim(reserved)))
-2)
AS int)
DESC
方法二：
1 SELECT object_name(id) tablename,
2 8*reserved/1024 reserved,
3 dpages,
4 rtrim(8*dpages/1024)+‘Mb‘ used,8*(reserved-dpages)/1024 unused, 8*dpages/1024-rows/1024*minlen/1024 free, rows,*
5 FROM sysindexes a
6 WHERE indid=1
7 ORDER BY a.reserved DESC
MSSQL数据库每个表占用的空间、大小
标签：表名 ges creat color objects pen rom l数据库 存储
