Database Size sp_MSforeachdb

,

This script find the size of the data and log file for every database on your server and add it to a table.  But it could do with some added sparkle!  Still it is simple to run, you might want to change the database from utils mind.

DROP TABLE TDB
create table tDB
(dbName varchar (255),
 dataSize float, 
 log_size float, 
 space_used float)

--db size
sp_MSforeachdb "INSERT INTO utils..tDB SELECT '?',(SUM(size)*8.0)/1024.0 ,0,0 FROM ?..sysfiles WHERE status & 0x40 <> 0x40"
go
--log size
sp_MSforeachdb "UPDATE utils..tDB SET log_size = (select (SUM(size)*8.0)/1024.0 FROM ?..sysfiles WHERE status & 0x40 = 0x40) where dbName = '?'"
go
--data space used
sp_MSforeachdb "UPDATE utils..tDB SET space_used=(select(SUM(reserved)*8.0)/1024.0 FROM ?..sysindexes WHERE indid IN(0,1,255)) where dbName='?'"
go

select * from utils..tDB

Rate

5 (1)

Share

Share

Rate

5 (1)