Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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  
 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.04 seconds. Powered By: Snitz Forums 2000