Sqlserver数据库表空间统计

查看数据库使用的空间

sp_spaceused @updateusage = 'TRUE'

 

查看所据库中所有表的使用空间

方法1: 调用系统的存储过程(返回多个表,不太好浏览)

exec sp_MSforeachtable "exec sp_spaceused '?'"

方法2: 先创建一个存储过程,再执行存储过程(也可不创建,直接执行存储过程内的语句)(返回一个表,关系较明了)

create procedure Sp_SpaceUsedView
as
begin
if not exists (select 0 from dbo.sysobjects where id = object_id(N'[dbo].[tablespaceinfo]') 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 from tablespaceinfo --清空数据表
    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 0 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

--显示数据库信息
    exec sp_spaceused @updateusage = 'TRUE'

--显示表信息
    select * from tablespaceinfo order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc

end

 

转载于:https://www.cnblogs.com/janas/archive/2011/12/18/2291676.html