Space Utilization for All Tables

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

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

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE sp_AllTableSpace AS

--Uses sp_tablespacecursor  to populate temp table with all table space data.

CREATE TABLE #temp
(
TableNamevarchar(50) NULL,
NumberOfRowsint NULL,
DataSpaceUsed       int NULL,
IndexSpaceUsed  int NULL
)

SET NOCOUNT ON

DECLARE @TableName varchar(50), 
@NumberOfRowsint,
@DataSpaceUsed       int,
@IndexSpaceUsed int


DECLARE gettable INSENSITIVE CURSOR FOR 

     Select name from sysobjects
where type = 'U'

FOR READ ONLY 

    OPEN gettable 


    FETCH NEXT FROM gettable INTO @TableName 
    WHILE (@@FETCH_STATUS = 0) 

BEGIN

DECLARE @recordlist CURSOR
EXECUTE sp_TableSpaceCursor @TableName,@recordlist OUTPUT

FETCH NEXT FROM @recordlist INTO @NumberOfRows, @DataSpaceUsed, @IndexSpaceUsed
    WHILE (@@FETCH_STATUS = 0) 

BEGIN

INSERT INTO #temp (TableName, NumberOfRows, DataSpaceUsed, IndexSpaceUsed)
VALUES (@TableName, @NumberOfRows, @DataSpaceUsed, @IndexSpaceUsed)


FETCH NEXT FROM @recordlist   INTO @NumberOfRows, @DataSpaceUsed, @IndexSpaceUsed
END  

CLOSE @recordlist
DEALLOCATE @recordlist


FETCH NEXT FROM gettable   INTO @TableName 

END  

CLOSE gettable 
DEALLOCATE gettable 

SET NOCOUNT OFF

Select * from #temp order by TableName ASC
Drop Table #temp
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE procedure sp_TableSpaceCursor
@name nvarchar(517), 
@recordlist CURSOR VARYING OUTPUT 
as

--SWB 31 December 2001
--returns a cursor containing the space utilization data for specified table.

SET NOCOUNT ON

declare @rows int, @datasizeused int, @indexsizeused int, @pagesize int
declare @dbname nvarchar(128)
declare @id int

select @dbname = db_name()

if (@id is null)
select @id = id from dbo.sysobjects where id = object_id(@name) and (OBJECTPROPERTY(id, N'IsTable') = 1)
if (@id is null)
begin
RAISERROR (15009, -1, -1, @name, @dbname)
return 1
end

/* rows */SELECT @rows = convert(int, rowcnt)
FROM dbo.sysindexes
WHERE indid < 2 and id = @id

/* data */SELECT @datasizeused =
(SELECT sum(dpages)
 FROM dbo.sysindexes
 WHERE indid < 2 and id = @id)
+
(SELECT isnull(sum(used), 0)
 FROM dbo.sysindexes
 WHERE indid = 255 and id = @id)

   /* Do not consider 2 < indid < 255 rows, those are nonclustered indices, and the space used by them are included by indid = 0(table) */   /* or indid = 1(clustered index) already.  indid = 0(table) and = 1(clustered index) are mutual exclusive *//* index */SELECT @indexsizeused =
(SELECT sum(used)
 FROM dbo.sysindexes
 WHERE indid in (0, 1, 255) and id = @id)
 - @datasizeused

/* Pagesize on this server (sysindexes stores size info in pages) */select @pagesize = v.low / 1024 from master..spt_values v where v.number=1 and v.type=N'E'



SET @recordlist = CURSOR 
FOR
select Rows = @rows, DataSpaceUsed = @datasizeused * @pagesize, IndexSpaceUsed = @indexsizeused * @pagesize

OPEN @recordlist


RETURN
GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO