Query to give me list of dbs + db size

How can I get a query that i can run against a SQL Server to get a list of
dbs and its size such as
Name Total DB Size Allocated DB Usage Total Log Size
Allocated Log Usage
DB1 120GB 100GB 20GB
1GB
DB2 1GB .7GB 50MB
25MB
master 100MB 50MB 100MB
15MB
...etc..
I would like to get a report in the same format as above.
Thanks
P.S. Using SQL 2000
Hassan,
Try chewing on this for a while.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
declare
@.dbName sysname,
@.sqlStr varchar(8000)
declare
@.DataFileP dec(15), @.DataFileB dec(19),
@.LogFileP dec(15), @.LogFileB dec(19),
@.ReservedP dec(15), @.ReservedB dec(19),
@.UserHeapP dec(15), @.UserHeapB dec(19),
@.SysHeapP dec(15), @.SysHeapB dec(19),
@.TableClusterP dec(15), @.TableClusterB dec(19),
@.ViewClusterP dec(15), @.ViewClusterB dec(19),
@.BlobP dec(15), @.BlobB dec(19),
@.IndexP dec(15), @.IndexB dec(19)
declare
@.bpp smallint -- bytes per page
select @.bpp = low
from master.dbo.spt_values
where number = 1
and type = 'E'
select
'dbName',
'DataFileB',
'LogFileB',
'ReservedB',
'UserHeapB',
'SysHeapB',
'TableClusterB',
'ViewClusterB',
'BlobB',
'IndexB',
'DataB',
'FreeB'
create table #Sizes (
[dbName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DataFileB] [decimal](19, 0) NULL ,
[LogFileB] [decimal](19, 0) NULL ,
[ReservedB] [decimal](19, 0) NULL ,
[UserHeapB] [decimal](19, 0) NULL ,
[SysHeapB] [decimal](19, 0) NULL ,
[TableClusterB] [decimal](19, 0) NULL ,
[ViewClusterB] [decimal](19, 0) NULL ,
[BlobB] [decimal](19, 0) NULL ,
[IndexB] [decimal](19, 0) NULL ,
[DataB] [decimal](22, 0) NULL ,
[FreeB] [decimal](24, 0) NULL
)
--^v^--
--^v^-- Start CursorProcessingDescription
--^v^--
declare Databases cursor
static forward_only
for
select name
from master.dbo.sysdatabases
order by name
open Databases
fetch next from Databases
into @.dbName
while (@.@.FETCH_STATUS = 0) begin
set @.sqlStr = REPLACE('
create view sysf_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysfiles
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view sysi_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysindexes
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
set @.sqlStr = REPLACE('
create view syso_spaceused as
select * from [DBDBDBDBDBDBDB].dbo.sysobjects
',
'DBDBDBDBDBDBDB', @.dbName)
exec (@.sqlStr)
-- Get Data File Space
select @.DataFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 = 0)
set @.DataFileB = @.DataFileP * @.bpp
-- Get Log File Space
select @.LogFileP = ISNULL(SUM(CONVERT(dec(15),size)), 0)
from sysf_spaceused
where (status & 64 != 0)
set @.LogFileB = @.LogFileP * @.bpp
-- Get Reserved Space
select @.ReservedP = ISNULL(SUM(CONVERT(dec(15),reserved)), 0)
from sysi_spaceused
where indid in (0, 1, 255)
set @.ReservedB = @.ReservedP * @.bpp
-- Get User Heap Space
select @.UserHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id in (select id from syso_spaceused where type = 'u')
set @.UserHeapB = @.UserHeapP * @.bpp
-- Get System Heap Space
select @.SysHeapP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 0
and id not in (select id from syso_spaceused where type = 'u')
set @.SysHeapB = @.SysHeapP * @.bpp
-- Get Table Clustered Index Space
select @.TableClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id not in (select id from syso_spaceused where type = 'v')
set @.TableClusterB = @.TableClusterP * @.bpp
-- Get Indexed View Clustered Index Space
select @.ViewClusterP = ISNULL(SUM(CONVERT(dec(15),dpages)), 0)
from sysi_spaceused
where indid = 1
and id in (select id from syso_spaceused where type = 'v')
set @.ViewClusterB = @.ViewClusterP * @.bpp
-- Get Blob Space
select @.BlobP = ISNULL(SUM(CONVERT(dec(15),used)), 0)
from sysi_spaceused
where indid = 255
set @.BlobB = @.BlobP * @.bpp
-- Get Index Space
select @.IndexP = SUM(CONVERT(dec(15),used)) - @.UserHeapP - @.SysHeapP -
@.TableClusterP - @.ViewClusterP - @.BlobP
from sysi_spaceused
where indid in (0, 1, 255)
set @.IndexB = @.IndexP * @.bpp
-- Report Results
-- select
-- @.dbName as dbName,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.DataFileB), 1), '.00',
'') as DataFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.LogFileB), 1), '.00',
'') as LogFileB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB), 1), '.00',
'') as ReservedB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB), 1), '.00', '')
as HeapB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.TableClusterB), 1),
'.00', '') as TableClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ViewClusterB), 1),
'.00', '') as ViewClusterB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.BlobB), 1), '.00', '')
as BlobB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.IndexB), 1), '.00', '')
as IndexB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.HeapB + @.TableClusterB +
@.ViewClusterB + @.BlobB), 1), '.00', '') as DataB,
-- REPLACE(CONVERT(varchar(32), CONVERT(money, @.ReservedB - (@.HeapB +
@.TableClusterB + @.ViewClusterB + @.BlobB + @.IndexB)), 1), '.00', '') as FreeB
insert #Sizes
select
@.dbName as dbName,
@.DataFileB as DataFileB,
@.LogFileB as LogFileB,
@.ReservedB as ReservedB,
@.UserHeapB as UserHeapB,
@.SysHeapB as SysHeapB,
@.TableClusterB as TableClusterB,
@.ViewClusterB as ViewClusterB,
@.BlobB as BlobB,
@.IndexB as IndexB,
@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB + @.BlobB as
DataB,
@.ReservedB - (@.UserHeapB + @.SysHeapB + @.TableClusterB + @.ViewClusterB +
@.BlobB + @.IndexB) as FreeB
drop view sysf_spaceused
drop view sysi_spaceused
drop view syso_spaceused
--^v^-- End CursorProcessingDescription
fetch next from Databases
into @.dbName
end
close Databases
deallocate Databases
--^v^-- End CursorProcessingDescription
select * from #Sizes
drop table #Sizes
-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
"Hassan" <hassan@.hotmail.com> wrote in message
news:u11AVVbYHHA.3996@.TK2MSFTNGP02.phx.gbl...
> How can I get a query that i can run against a SQL Server to get a list of
> dbs and its size such as
> Name Total DB Size Allocated DB Usage Total Log Size
> Allocated Log Usage
> DB1 120GB 100GB 20GB 1GB
> DB2 1GB .7GB 50MB
> 25MB
> master 100MB 50MB 100MB 15MB
> ...etc..
> I would like to get a report in the same format as above.
> Thanks
> P.S. Using SQL 2000
>