sqlserver數(shù)據(jù)庫文件大小

QQ1355017510
 電話  0371-63322099


第一種方法(較簡單，看的有些吃力)：

exec sp_MSforeachtable "exec sp_spaceused '?'"

第二種方法(較復雜，但看的比較清楚，原作者不詳)：

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)建結果存儲表
(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

服務器租用/服務器托管中國五強！虛擬主機域名注冊頂級提供商！15年品質保障！--億恩科技[ENKJ.COM]