sp_spaceused
顯示行數(shù)、保留的磁盤空間以及當(dāng)前數(shù)據(jù)庫中的表所使用的磁盤空間，或顯示由整個數(shù)據(jù)庫保留和使用的磁盤空間。
語法
sp_spaceused [[@objname =] 'objname']
[,[@updateusage =] 'updateusage']
參數(shù)
[@objname =] 'objname'
是為其請求空間使用信息（保留和已分配的空間）的表名。objname 的數(shù)據(jù)類型是 nvarchar(776)，默認(rèn)設(shè)置為 NULL。
[@updateusage =] 'updateusage'
表示應(yīng)在數(shù)據(jù)庫內(nèi)（未指定 objname 時）還是在特定的對象上（指定 objname 時）運(yùn)行 DBCC UPDATEUSAGE。值可以是 true 或 false。updateusage 的數(shù)據(jù)類型是 varchar(5)，默認(rèn)設(shè)置為 FALSE。
返回代碼值
0（成功）或 1（失敗）
示例
A. 有關(guān)表的空間信息
下例報告為 titles 表分配（保留）的空間量、數(shù)據(jù)使用的空間量、索引使用的空間量以及由數(shù)據(jù)庫對象保留的未用空間量。
USE pubs
EXEC sp_spaceused 'titles'

B. 有關(guān)整個數(shù)據(jù)庫的已更新空間信息
下例概括當(dāng)前數(shù)據(jù)庫使用的空間并使用可選參數(shù) @updateusage。
USE pubs
sp_spaceused @updateusage = 'TRUE'
不過此方法，只能查看一個表的大小，一個數(shù)據(jù)庫中一般會有多個表，如何一次性查看某數(shù)據(jù)庫的所有表大小呢？

第一種方法(較簡單，看的有些吃力)：
exec sp_MSforeachtable "exec sp_spaceused '?'"

第二種方法(較復(fù)雜，但看的比較清楚，原作者不詳)：
IF NOT EXISTS (
????????? SELECT
????????? *
????????? FROM
????????? dbo.sysobjects
????????? WHERE
????????? id = object_id(N'[dbo].[tablespaceinfo]')
????????? AND OBJECTPROPERTY(id, N'IsUserTable') = 1
?????? )
?????? CREATE TABLE tablespaceinfo --創(chuàng)建結(jié)果存儲表
??????
?????? (
?????????? nameinfo VARCHAR(50),
?????????? rowsinfo INT,
?????????? reserved VARCHAR(20),
?????????? datainfo VARCHAR(20),
?????????? index_size VARCHAR(20),
?????????? unused VARCHAR(20)
?????? )
??????
?????? DELETE FROM tablespaceinfo --清空數(shù)據(jù)表
?????? DECLARE @tablename VARCHAR(255) --表名稱
?????? DECLARE @cmdsql VARCHAR(500)
?????? DECLARE Info_cursor CURSOR FOR
????????????????????????????????? SELECT
????????????????????????????????? ?? o.name
????????????????????????????????? FROM
????????????????????????????????? ?? dbo.sysobjects o
????????????????????????????????? WHERE
????????????????????????????????? ?? OBJECTPROPERTY(o.id, N'IsTable') = 1
????????????????????????????????? AND o.name NOT LIKE N'#%%'
????????????????????????????????? ORDER BY
????????????????????????????????? ?? o.name
????????????????????????????????? OPEN Info_cursor
????????????????????????????????? FETCH NEXT FROM Info_cursor
????????????????????????????????? INTO @tablename
????????????????????????????????? WHILE @@FETCH_STATUS = 0
????????????????????????????????? BEGIN
????????????????????????????????? ???? IF EXISTS (
?????????????????? SELECT
????????????????????????????????? ?????????? *
????????????????????????????????? ?????????? FROM
????????????????????????????????? ?????????? dbo.sysobjects
????????????????????????????????? ?????????? WHERE
????????????????????????????????? ?????????? id = object_id(@tablename)
????????????????????????????????? ?????????? 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 FROM Info_cursor
????????????????????????????????? ??????? INTO @tablename
????????????????????????????????? ???? END CLOSE Info_cursor
????????????????????????????????? ???? DEALLOCATE Info_cursor
GO
--itlearner注：顯示數(shù)據(jù)庫信息
sp_spaceused @updateusage = 'TRUE'
--itlearner注：顯示表信息
SELECT
*
FROM
tablespaceinfo
ORDER BY
CAST(LEFT(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) AS INT) desc

第三種方法：
SELECT
object_name(id) tablename,
8*reserved/1024 reserved,
rtrim(8*dpages/1024)+'Mb' used,
8*(reserved-dpages)/1024 unused,
8*dpages/1024-ROWS/1024*minlen/1024 FREE,
ROWS,
*
FROM
sysindexes
WHERE
indid = 1
ORDER BY
reserved desc

轉(zhuǎn)載于:https://www.cnblogs.com/wonder315/archive/2011/09/12/2173938.html

總結(jié)

以上是生活随笔為你收集整理的【转载】查看MSSQL数据库每个表占用的空间大小的方法的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。