Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sp_spaceused tablename for each database


sp_spaceused tablename for each database

Author
Message
sbauer
sbauer
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 1

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





sbauer
sbauer
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 1

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





mssql_rules
mssql_rules
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 1

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,





mssql_rules
mssql_rules
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 1

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





Amit Jethva
Amit Jethva
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 52

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







-- Amit



"There is no 'patch' for stupidity."



Download the Updated SQL Server 2005 Books Online.
sbauer
sbauer
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 1

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





sbauer
sbauer
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 1

The result's I used were from amitjethva.

Thanks in advance





mssql_rules
mssql_rules
SSC Veteran
SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)SSC Veteran (221 reputation)

Group: General Forum Members
Points: 221 Visits: 1

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.





Amit Jethva
Amit Jethva
SSC Eights!
SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)SSC Eights! (847 reputation)

Group: General Forum Members
Points: 847 Visits: 52

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






-- Amit



"There is no 'patch' for stupidity."



Download the Updated SQL Server 2005 Books Online.
sbauer
sbauer
SSC Veteran
SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)SSC Veteran (288 reputation)

Group: General Forum Members
Points: 288 Visits: 1

Two words for you Amit..

YOU ROCK!!!

thanks soo much for your help.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search