alter 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 nvarchar(50) ,rowsinfo int , reserved nvarchar(20) ,datainfo nvarchar(20),index_size nvarchar(20) ,unused nvarchar(20)
)
delete from tablespaceinfo
declare @tablename nvarchar(255)
declare @cmdsql nvarchar(500)
DECLARE Info_cursor CURSOR FOR
select o.name from dbo.sysobjects o where OBJECTPROPERTY(o.id, N'IsTable') = 1 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
exec get_tableinfo
select *from tablespaceinfo order by cast(left(ltrim(rtrim(reserved)) , len(ltrim(rtrim(reserved)))-2) as int) desc
