1、建立存储过程
2、执行存储过程进行表容量统计
3、查看统计结果
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
代码
View Code
CREATE
PROCEDURE get_tableinfo
AS
if not exists ( select * 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 * 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
if not exists ( select * 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 * 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
2、执行存储过程进行表容量统计
代码
exec get_tableinfo
3、查看统计结果
代码
select *
from tablespaceinfo
order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc