List sizes of tables in database

go
/*******************************************************************************

  Written By  : Simon Sabin
  Date        : 25 October 2002
  Description : Returns the spaceused by all tables in a database
              : 
  History
  Date       Change
  ------------------------------------------------------------------------------
  25/10/2002 Created
*******************************************************************************/
SET NOCOUNT ON
DECLARE @SetOption bit, @databasename varchar(30), @orderCol varchar(30), @numeric bit

/*******************************************************************************
--Change this to change the way data is ordered
*******************************************************************************/SELECT @orderCol = 'data'

SELECT @DatabaseName = db_name()
SELECT @numeric = 1

IF @DatabaseName <> 'Master' 
   AND NOT EXISTS (select 1 from master..sysdatabases WHERE name = @DatabaseName AND (status & 4) = 4)
  BEGIN
  exec sp_dboption @databaseName ,'select into/bulkcopy', 'true'
  SELECT @SetOption = 1
  END

IF EXISTS (SELECT 1 FROM master..sysobjects WHERE name = 'space1')
  DROP TABLE master..space1
CREATE TABLE master..Space1 (name varchar(60), rows varchar(11), reserved varchar(11), data varchar(11), index_size varchar(11), unused varchar(11))

DECLARE @Cmd varchar(255)
declare cSpace CURSOR FOR
  select 'USE ' +@DatabaseName + ' INSERT into master..space1 EXEC sp_spaceUsed ''[' + u.name + '].[' + o.name + ']'''
  FROM sysobjects o
  join sysusers u on u.uid = o.uid
  WHERE type = 'U' 
  AND o.Name <> 'Space1'

OPEN cSPACE
FETCH cSpace INTO @Cmd
WHILE @@FETCH_STATUS =0
  BEGIN
--  PRINT @Cmd
  EXECUTE (@Cmd)
  FETCH cSpace INTO @Cmd
  END
DEALLOCATE cSPace

SELECT Description,
       Rows,
       Reserved,
       Data,
       Index_size,
       dataPerRows
FROM (
  SELECT 3 DataOrder,
         CONVERT(int,CASE @OrderCol WHEN 'Rows' THEN Rows
                          WHEN 'Reserved' THEN SUBSTRING(Reserved, 1,LEN(Reserved)-2)
                          WHEN 'data' THEN SUBSTRING(Data, 1,LEN(Data)-2)
                          WHEN 'index_size' THEN SUBSTRING(Index_size, 1,LEN(index_Size)-2)
                          WHEN 'unused' THEN SUBSTRING(unused, 1,LEN(unused)-2) END) OrderData, 
         name Description, 
         rows, 
         CASE @NUMERIC WHEN 0 THEN reserved ELSE SUBSTRING(reserved, 1, len(reserved)-2) END reserved, 
         CASE @NUMERIC WHEN 0 THEN data ELSE SUBSTRING(data, 1, len(data)-2) END data, 
         CASE @NUMERIC WHEN 0 THEN index_size ELSE SUBSTRING(index_size, 1, len(index_size)-2) END index_size,
         --SUBSTRING(data, 1, len(data)-2) DataPerRows
--CONVERT(numeric(19,6),SUBSTRING(data, 1, len(data)-2)) /rows dataPerRows
         CASE WHEN Rows = 0 THEN '' ELSE CONVERT(varchar(11),CONVERT(numeric(10,2),CONVERT(numeric,SUBSTRING(reserved, 1, len(reserved)-2)) /rows*1000)) END DataPerRows
    FROM master..Space1 
  UNION ALL
  SELECT 1 DataOrder, 0 OrderData,
         CONVERT(varchar(30),'Total' ) Description,
         CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows, 
         CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved, 
         CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data, 
         CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
         ''
  FROM master..space1
  UNION ALL
  SELECT 2, 0,
         REPLICATE('-',30),
         REPLICATE('-',11),
         REPLICATE('-',11),
         REPLICATE('-',11),
         REPLICATE('-',11),
         REPLICATE('-',11)
  UNION ALL
  SELECT 4,0,
         REPLICATE('-',30),
         REPLICATE('-',11),
         REPLICATE('-',11),
         REPLICATE('-',11),
         REPLICATE('-',11),
         REPLICATE('-',11)
  UNION ALL
  SELECT 5,0,
         CONVERT(varchar(30),'Total' ) Description,
         CONVERT(varchar(11),SUM(CONVERT(int,Rows))) Rows, 
         CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Reserved, 1,LEN(Reserved)-2)))) Reserved, 
         CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(Data, 1,LEN(Data)-2)))) Data, 
         CONVERT(varchar(11),SUM(CONVERT(int,SUBSTRING(index_size, 1,LEN(Index_size)-2)))) Index_Size,
         ''
  FROM master..space1 ) Stuff
ORDER BY DataOrder, OrderData desc, description

EXECUTE ('DROP TABLE master..space1')
IF @SetOption = 1
  exec sp_dboption @databasename ,'select into/bulkcopy', 'false'
GO