一、查询某个数据表占用空间大小
code:
Exec sp_spaceused ‘表名称’
二、循环读取数据库中所有表,并查询出每张表所占用的空间大小
code:
--建立一个临时表
use [dbName]
create table #TabSpaceUsed(
name nvarchar(100),
row char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
--循环数据库查询某个表所占空间大小
Exec sp_MSforeachtable 'insert into #TabSpaceUsed Exec sp_spaceused ''?'''
--查询结果
select * from #TabSpaceUsed
--删除临时表
drop table #TabSpaceUsed
--另一种方法
SELECT
TableName = obj.name,
TotalRows = prt.rows,
[SpaceUsed(KB)] = SUM(alloc.used_pages)*8
FROM sys.objects obj
JOIN sys.indexes idx on obj.object_id = idx.object_id
JOIN sys.partitions prt on obj.object_id = prt.object_id
JOIN sys.allocation_units alloc on alloc.container_id = prt.partition_id
WHERE obj.type = 'U' AND idx.index_id IN (0, 1)
GROUP BY obj.name, prt.rows
ORDER BY TableName
三、查询出若干条数据记录所占空间大小
code:
--建立一个临时表
use [dbName]
create table #TabSpaceUsed(
name nvarchar(100),
row char(11),
reserved varchar(18),
data varchar(18),
index_size varchar(18),
unused varchar(18)
)
select * into #temp from [tableName] --where 条件
Exec('use tempdb;insert into #TabSpaceUsed exec sp_spaceused ''#temp'' drop table #temp')
select * from #TabSpaceUsed --查询结果
四、定义一些变量,查询其所占大小
code:
declare @t nvarchar(200)
declare @t1 numeric(15, 2)
set @t='新建文本文档文档.txt'
set @t1=13.09234
select datalength(@t)--所占字节
select datalength(@t1)--所占字节