sp_spaceused for multiple tables

[mutley1]

Hi all,

I have a DB with approx 100 tables and want to run a sp_spaceused on all of them to fine the larger ones so they can be cleared down. Only using QA at the moment so unless I run them 1 by 1, I'm running:

sp_spaceused tbl1
GO
sp_spaceused tbl2
GO
sp_spaceused tbl3
GO
sp_spaceused tbl4
GO etc. etc. etc.

However, the results are returned one by one.

Is there a way to concatenate them all (i.e. so I can just copy & paste into Excel) or am I going to have to set up a temp table, insert the results and then extracy via select all from that?

Thx,

M.

 

[vongrunt]

Try this:

create table #blah (name nvarchar(20), rows char(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
exec sp_msforeachtable 'insert into #blah exec sp_spaceused ''?'''
select * from #blah order by name
drop table #blah

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[mutley1]

Thanks vongrunt. Also just found an SP (was searching last night but gave up & had fresh start this morning!!!). Posted below if it would ever come in handy for you. Does pretty much the same thing.....

Cheers,

M.

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

Create procedure sp_spaceused_all
@SourceDB varchar(128)
as
/*
exec sp_spaceused_all 'mydb'
*/

set nocount on

declare @sql varchar(128)
create table #tables(name varchar(128))

select @sql = 'insert #tables select TABLE_NAME from ' + @SourceDB + '.INFORMATION_SCHEMA.TABLES where TABLE_TYPE = ''BASE TABLE'''
exec (@sql)

create table #SpaceUsed (name varchar(128), rows varchar(11), reserved varchar(18), data varchar(18), index_size varchar(18), unused varchar(18))
declare @name varchar(128)
select @name = ''
while exists (select * from #tables where name > @name)
begin
select @name = min(name) from #tables where name > @name
select @sql = 'exec ' + @SourceDB + '..sp_executesql N''insert #SpaceUsed exec sp_spaceused ' + @name + ''''
exec (@sql)
end
select * from #SpaceUsed
drop table #tables
drop table #SpaceUsed
go