lzg

2308 积分	0 好友	259 主题

发消息

查看SQL数据库中每张表所占空间的方法

楼主

发表于 2015-5-13 20:35:54 | 查看: 2540| 回复: 3

IF NOT EXISTS ( SELECT *
FROM sys.tables
WHERE name = 'tablespaceinfo' )
BEGIN
CREATE TABLE tablespaceinfo --创建结果存储表
(
Table_Name VARCHAR(50) ,
Rows_Count INT ,
reserved INT ,
datainfo INT ,
index_size INT ,
unused INT
)
END
DELETE FROM tablespaceinfo
--清空数据表
CREATE TABLE #temp --创建结果存储表
(
nameinfo VARCHAR(50) ,
rowsinfo INT ,
reserved VARCHAR(20) ,
datainfo VARCHAR(20) ,
index_size VARCHAR(20) ,
unused VARCHAR(20)
)
DECLARE @tablename VARCHAR(255)
--表名称
DECLARE @cmdsql NVARCHAR(500)
DECLARE Info_cursor CURSOR
FOR
SELECT '[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']' AS Table_Name
FROM [INFORMATION_SCHEMA].[TABLES]
WHERE TABLE_TYPE = 'BASE TABLE'
AND TABLE_NAME <> 'tablespaceinfo'
OPEN Info_cursor
FETCH NEXT FROM Info_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmdsql = 'insert into #temp exec sp_spaceused ''' + @tablename
+ ''''
EXECUTE sp_executesql @cmdsql
FETCH NEXT FROM Info_cursor
INTO @tablename
END
CLOSE Info_cursor
DEALLOCATE Info_cursor
GO
--itlearner注：显示数据库信息
--sp_spaceused @updateusage = 'TRUE'
--itlearner注：显示表信息
UPDATE #temp
SET reserved = REPLACE(reserved, 'KB', '') ,
datainfo = REPLACE(datainfo, 'KB', '') ,
index_size = REPLACE(index_size, 'KB', '') ,
unused = REPLACE(unused, 'KB', '')
INSERT INTO dbo.tablespaceinfo
SELECT nameinfo ,
CAST(rowsinfo AS INT) ,
CAST(reserved AS INT) ,
CAST(datainfo AS INT) ,
CAST(index_size AS INT) ,
CAST(unused AS INT)
FROM #temp
DROP TABLE #temp
SELECT Table_Name ,
Rows_Count ,
CASE WHEN reserved > 1024
THEN CAST(reserved / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(reserved AS VARCHAR(10)) + 'KB'
END AS Data_And_Index_Reserved ,
CASE WHEN datainfo > 1024
THEN CAST(datainfo / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(datainfo AS VARCHAR(10)) + 'KB'
END AS Used ,
CASE WHEN Index_size > 1024
THEN CAST(index_size / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(index_size AS VARCHAR(10)) + 'KB'
END AS index_size ,
CASE WHEN unused > 1024 THEN CAST(unused / 1024 AS VARCHAR(10)) + 'Mb'
ELSE CAST(unused AS VARCHAR(10)) + 'KB'
END AS unused
FROM dbo.tablespaceinfo
ORDER BY reserved DESC

复制代码

SQL数据库, create, 空间

回复 支持0 反对0

相关帖子

• eclipse中将java项目转换为web项目

收藏0 回复只看该作者道具举报

lzg

沙发

发表于 2015-5-13 20:37:54

Ｃreate Ｔable #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))

回复只看该作者道具举报

匿名 27.10.146.x

板凳

匿名 发表于 2015-5-13 20:59:10

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

回复道具

lzg

地板

发表于 2015-9-16 17:10:00

--SQL 2000方法：
create table tmp (name varchar(50),rows int,reserved varchar(50),
data varchar(50),index_size varchar(50),unused varchar(50))
insert into tmp (name,rows,reserved,
data,index_size,unused) exec sp_msforeachTable @Command1="sp_spaceused '?'"--sp_spaceused 't_vehicle'
select * from tmp order by data desc
drop table tmp

回复只看该作者道具举报

返回列表

		自动登录	找回密码
密码			立即注册