I've been asked to put something together that will go through EVERY database on a server and put the output of the sp_spaceused 'tablename' into another table, so we can monitor table growth. I've found lots of ways of doing it in only ONE database, but need to do it in every database and my SQL Skills are somewhat lacking... Here is the output from a GREAT script I found on this site. It does everything I need, but go through each database and add the database name to the table. I've been trying to figure out how to do it via a cursor but can't seem to get it right..

I've attached the script of Simon Sabin (thanks Simon), if anyone can even give me hints or clues it would be greatly appreciated.

Description   Rows      Reserved    Data         Index_size  dataPerRows
---------- --------- ----------- ----------- ----------- -----------
Total          3375693     381456    267320      113776     
------ ----------- ----------- ----------- ----------- -----------
covquote    3148601     334800      228032      106736      106.33
vehicle        55747       15504       14496        968         278.11
driver          57115       10384       8264        2024        181.81
Test1          57115       10384       8264        2024        181.81
Test2          57115       10384       8264        2024        181.81
testTable      0           0           0           0          
---------  ----------- ----------- ----------- ----------- -----------
Total    3375693     381456      267320      113776   

Sorry forgot to add the script

/*******************************************************************************

  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 = 'master' 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 @orderCol = 'data'
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

The table sysindexes has most of the information generated by the sp_spaceused. Querying sysindexes is probably easy, since you get space usage for the entire database at one shot. Iterating on the table is easy through cursor.

Here is a link to the MS article that explains how to interpret the values from sysindexes table.

http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q90/7/58.asp&NoWebContent=1

Let us know if that has been helpful.

Regards,

Here is a piece of code I found on some other site that does (I did not validate the code, please verify and modify if required)

Make this a procedure in the master database, something like sp_MySpaceused and execute the procedure for each database through a cursor.

declare
 @id     int               
,@type     character(2)           
,@pages     int               
,@dbname sysname
,@dbsize dec(15,0)
,@bytesperpage     dec(15,0)
,@pagesperMB          dec(15,0)

create table #spt_space
(
     objid          int null,
     rows          int null,
     reserved     dec(15) null,
     data          dec(15) null,
     indexp          dec(15) null,
     unused          dec(15) null
)

set nocount on

-- Create a cursor to loop through the user tables
declare c_tables cursor for
select     id
from     sysobjects
where     xtype = 'U'

open c_tables

fetch next from c_tables
into @id

while @@fetch_status = 0
begin

     /* Code from sp_spaceused */
     insert into #spt_space (objid, reserved)
          select objid = @id, sum(reserved)
               from sysindexes
                    where indid in (0, 1, 255)
                         and id = @id

     select @pages = sum(dpages)
               from sysindexes
                    where indid < 2
                         and id = @id
     select @pages = @pages + isnull(sum(used), 0)
          from sysindexes
               where indid = 255
                    and id = @id
     update #spt_space
          set data = @pages
     where objid = @id


     /* index: sum(used) where indid in (0, 1, 255) - data */
     update #spt_space
          set indexp = (select sum(used)
                    from sysindexes
                    where indid in (0, 1, 255)
                    and id = @id)
                   - data
          where objid = @id

     /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
     update #spt_space
          set unused = reserved
                    - (select sum(used)
                         from sysindexes
                              where indid in (0, 1, 255)
                              and id = @id)
          where objid = @id

     update #spt_space
          set rows = i.rows
               from sysindexes i
                    where i.indid < 2
                    and i.id = @id
                    and objid = @id

     fetch next from c_tables
     into @id
end


select
     Table_Name = (select left(name,25) from sysobjects where id = objid),
     rows = convert(char(11), rows),
     reserved_KB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
     data_KB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
     index_size_KB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
     unused_KB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')          
from      #spt_space, master.dbo.spt_values d
where      d.number = 1
and      d.type = 'E'
order by reserved desc

drop table #spt_space
close c_tables
deallocate c_tables

This is the modified version of script that you have attached.

create both procs in master database. (Note the use of  @db + '..sp_MySpaceUsed ' in sp_MySpaceUsed_AllDB procedure)
 

-- Amit

create proc sp_MySpaceUsed_AllDB
as

declare @cmd varchar(255) , @db sysname
declare db cursor for
 select name from master..sysdatabases where dbid > 4

open db
fetch db into @db
while @@fetch_status =0
  begin
   set @cmd =  @db + '..sp_MySpaceUsed '
   execute (@cmd)
   fetch db into @db
  end
close db
deallocate db

create proc sp_MySpaceUsed
as
/*******************************************************************************

  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
  19/02/2004 added database name in select list
*******************************************************************************/

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 = 'master' 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 @ordercol = 'data'
select db_name(), 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

Thanks.. that is EXACTLY what I'm looking. Now my only issue is putting the info into a table. Every time I try an insert I get an error message that says

Server: Msg 8164, Level 16, State 1, Line 1
An INSERT EXEC statement cannot be nested.
Server: Msg 8164, Level 16, State 1, Line 1

I've tried using adding it to the SP's or as a seperate statement and receive the same error.. I'm getting VERY frustrated..

Any more help would be appreciated.

Thanks

Susan

The result's I used were from amitjethva.

Thanks in advance

I think it relates to SQL Server not allowing nested inserts in a Insert into .. Exec construct. That is why instead of using the "insert into master..space1 exec sp_spaceused " construct yank the code from the proc and use it directly, ie, make a new procedure that has the code from sp_spaceused and inserts into the table directly. And then you can call the code for each object, DB etc.

Or, use the sysindexes and do it for the entire database at one shot.

Try this ...

-- Amit

alter proc sp_MySpaceUsed_AllDB
as
declare @cmd varchar(255) , @db sysname
declare db cursor for
select name from master..sysdatabases where dbid > 4

if exists (select 1 from master..sysobjects where name = 'AllDBSpace')
  drop table master..AllDBSpace
create table master..AllDBSpace (dbname sysname ,tabname varchar(60), rows varchar(100), reserved varchar(100), data varchar(100), index_size varchar(100), dataperrow varchar(100))

open db
fetch db into @db
while @@fetch_status =0
  begin
   set @cmd =    'exec '+ @db + '..sp_MySpaceUsed '
   execute (@cmd)
   fetch db into @db
  end
close db
deallocate db

select * from AllDBSpace
go

alter proc sp_MySpaceUsed
as
/*******************************************************************************

  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
  19/02/2004 added database name in select list
*******************************************************************************/

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 = 'master' 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 @ordercol = 'data'
insert AllDBSpace 
select db_name(), 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

Two words for you Amit..

YOU ROCK!!!

thanks soo much for your help.