SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 sp_msforeachtable meets sp_mstablespace
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Jay99
Constraint Violating Yak Guru

USA
468 Posts

Posted - 03/14/2002 :  16:39:33  Show Profile  Reply with Quote

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[usp_tablespace]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[usp_tablespace]
GO



CREATE procedure usp_tablespace
@database varchar(255)
AS
begin
set nocount on
declare @row int, @maxrow int, @tablename varchar(255)
declare @name varchar(255), @rows int, @dataspaceused int, @indexspaceused int
declare @totaldataspace int, @totalindexspace int
declare @sql nvarchar(4000)
set @row = 1

create table #space
(
rowid int identity,
name varchar(255),
rows integer,
dataspaceused integer,
indexspaceused integer
)

create table #space2
(
rows integer,
dataspaceused integer,
indexspaceused integer
)

SET @SQL = 'USE ' + @database + ' insert into #space(name)
exec sp_msforeachtable ''select ''''?'''''''

exec sp_executesql @sql

select @maxrow = max(rowid)
from #space

while @row <= @maxrow
begin
select @tablename = name
from #space
where rowid = @row

set @sql = 'use ' + @database + ' insert #space2 (rows,dataspaceused,indexspaceused)
exec sp_mstablespace @name = ''' + @tablename + ''''

exec sp_executesql @sql

select @name = @tablename, @rows = rows, @dataspaceused = dataspaceused, @indexspaceused = indexspaceused
from #space2

update #space
set rows = ISNULL(@rows,0), dataspaceused = ISNULL(@dataspaceused,0), indexspaceused = ISNULL(@indexspaceused,0)
where name = @name

delete #space2

set @row = @row + 1
end

select sum(dataspaceused) as 'Total User DataSpace', sum(indexspaceused) as 'Total User IndexSpace'
from #space

select @totaldataspace = sum(dataspaceused)
from #space
select @totalindexspace = sum(indexspaceused)
from #space

select name,rows,dataspaceused,indexspaceused,
convert(decimal(5,2),convert(float,dataspaceused)/convert(float,@totaldataspace)) as 'percent dataspace',
convert(decimal(5,2),convert(float,indexspaceused)/convert(float,@totalindexspace)) as 'percent indexspace'
from #space
order by dataspaceused desc

drop table #space
drop table #space2

RETURN
end



GO




Jay

Edited by - Jay99 on 04/05/2002 09:30:38
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000