sp_spaceused tablename for each database
-
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.
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.
-
Just FYI I have submitted an article to Brian with scripts that capture this information into a historical DB, them move the data from each server to a central site, if desired.
Haven't heard back, but it may appeared in a future issue of the SQLServerCentral mag. I assume the code will be posted up here somewhere.
The second part which I am working slowly to complete, captures changes to various user objects. SPs, Views and UDFs.
KlK -
I just saw your question online, I am sorry for being late to give an answer, but the following query allow you to iterate through each table in each database excluding system databases, you can do anything you want with each table by changing the @command1 parameter of sp_MSforeachtable stored procedure, check it out and let me know what you think:
DECLARE @SQL NVarchar(4000)
SET @SQL = ''
SELECT @SQL = @SQL + ' PRINT ''==' + Name + '=='' EXEC ' + NAME + '..sp_MSforeachtable @command1=''PRINT ''''*'''' '', @replacechar=''*''' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid > 6 --exclude system databases
EXEC (@SQL)
This query print tables names in each database
Mohamed Benothmane
-
And Expanding on Mohammed Excellent idea:
DECLARE @SQL NVarchar(4000)
if OBJECT_ID('tempdb..#T1') IS NOT NULL
drop table #T1
create table #T1 ([name] varchar(60)
, [rows] varchar(11)
, reserved varchar(11)
, data varchar(11)
, index_size varchar(11)
, unused varchar(11)
, DBName nvarchar(128))
SET @SQL = ' SET NOCOUNT ON '
SELECT @SQL = @SQL + ' INSERT INTO #T1 ([name], [rows], reserved, data , index_size , unused ) EXEC ' + NAME + '..sp_MSforeachtable @command1=''sp_Spaceused''''*'''' '', @replacechar=''*'', @PostCommand='' Update #T1 Set DBName = N'''''+ NAME + ''''' where #T1.DBName IS NULL '' ' + Char(13)
FROM MASTER..Sysdatabases
WHERE dbid > 4 --exclude system databases
EXEC (@SQL)
--Print @SQL
SELECT *
FROM #T1
ORDER BY DBNAME,[Name]
Will give what you want !!!
* Noel
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply