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