How to get sp_spaceused to report all tables and DB''s

Question

Post reply

How to get sp_spaceused to report all tables and DB''s

Cory E.

SSCertifiable

Points: 7076
More actions
August 30, 2006 at 7:42 pm

#114403

Here is the general idea.

I need to get (and store for trending) size of every table in every database (for every server). This needs to be retained in a table to be queried against. I have two out of three of these things covered...

I have every server (Job that runs daily that populates the table - linked server together to get all the data together)

I have ever table...here is the basic run down:

Create a temp table, then run sp_msforeachtable inserting into the temp table - Then, a few updates to make the data readable, then insert the data into the final storage table. This works, very well...except that it only works for the database this runs on...it cannot run on other databases, even if the use <database> command. here is the basic SQL:

create

procedure GetTableData1 as

create

table #tmpTableData (

Server varchar(80) default @@servername,

DatabaseName

varchar(80) null,

DateRun

varchar(25) default getdate(),

Name varchar(60) not null,

Rows

int not null,

Reserved

varchar(20) not null,

Data

varchar(20) not null,

Index_Size

varchar(20) not null,

Unused

varchar(20) not null

)

exec

sp_msforeachtable "insert into #tmpTableData (Name, Rows, Reserved, Data, Index_Size, Unused) exec sp_spaceused '?'"

update

#tmpTableData

set

Reserved = left(Reserved, len(Reserved) - 3), Data = left(Data, len(Data) - 3),

Index_Size

= left(Index_Size, len(Index_Size) - 3), Unused = left(Unused, len(Unused) - 3)

alter

table #tmpTableData

alter column Reserved int

alter

table #tmpTableData

alter column Data int

alter

table #tmpTableData

alter column Index_Size int

alter

table #tmpTableData

alter column Unused int

update

#tmpTableData

set [databasename] = db_name()

where [databasename] is null

IF

EXISTS (SELECT name FROM sysobjects WHERE name = 'DB_Table_Info' AND type = 'U')

begin

Insert

into DB_Table_Info

select

* from #tmpTableData

order

by Reserved desc

end

else

begin

select

* into DB_Table_Info from #tmpTableData order by Reserved desc

end

drop

table #tmpTableData

I even tried sp_msforeachdb "GetTableData1", but that does not work...the closest I came was getting 5 copies of each row, one for each database.

Maybe I have been working on this to hard all day, and missed something totally obvious...that would not bother me anymore...

TIA

Cory

-- Cory
September 4, 2006 at 8:00 am

This was removed by the editor as SPAM