use master
go

CREATE procedure sp_dbspace
as
/* SQL7 and SQ2000 */
set nocount on

create table #results(FileType    varchar(4) NOT NULL,
                      [Name]      sysname NOT NULL,
                      Total       numeric(9,2) NOT NULL,
                      Used        numeric(9,2) NOT NULL,
                      [Free]      numeric(9,2) NOT NULL)

create table #data(Fileid       int NOT NULL,
                   [FileGroup]  int NOT NULL,
                   TotalExtents int NOT NULL,
                   UsedExtents  int NOT NULL,
                   [Name]       sysname NOT NULL,
                   [FileName]   varchar(300))

create table #log(DatabaseName sysname NOT NULL,
                  LogSize      numeric(15,7) NOT NULL,
                  LogUsed      numeric(9,5) NOT NULL,
                  Status       int NOT NULL)

/* Get data file(s) size */
insert #data
exec('DBCC showfilestats') 

/* Get log file(s) size */
insert #log
exec('dbcc sqlperf(logspace)')

insert #results(FileType,[Name],Total,Used,[Free])
select 'Data',
       left(right([FileName],charindex('\',reverse([FileName]))-1),
            charindex('.',right([FileName],
            charindex('\',reverse([FileName]))-1))-1),
       CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
       CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
       (CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
       -CAST(((UsedExtents*64)/1024.00) as numeric(9,2)))
from   #data

union all

select 'Log',DatabaseName+'_log',LogSize,
       ((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize)
from   #log
where  DatabaseName = db_name()

select * from #results

drop table #data
drop table #log
drop table #results
return


--improved below but sql2000 only due to sysaltfiles


use master
go

CREATE procedure sp_dbspaceall(@all char(1)='N')
as

set nocount on

declare @cmd varchar(500)
declare @db varchar(128)

create table #results(FileType    varchar(4) NOT NULL,
                      [Name]      sysname NOT NULL,
                      Total       numeric(9,2) NOT NULL,
                      Used        numeric(9,2) NOT NULL,
                      [Free]      numeric(9,2) NOT NULL,
                      dbname      sysname NULL)

create table #data(Fileid       int NOT NULL,
                   [FileGroup]  int NOT NULL,
                   TotalExtents int NOT NULL,
                   UsedExtents  int NOT NULL,
                   [Name]       sysname NOT NULL,
                   [FileName]   varchar(300) NOT NULL)

create table #log(dbname       sysname NOT NULL,
                  LogSize      numeric(15,7) NOT NULL,
                  LogUsed      numeric(9,5) NOT NULL,
                  Status       int NOT NULL)

If @all='N'   /* just the current database */
begin
   /* Get data file(s) size */
   insert #data
   exec('DBCC showfilestats') 
   
   /* Get log file(s) size */
   insert #log
   exec('dbcc sqlperf(logspace)')
   
   insert #results(FileType,[Name],Total,Used,[Free])
   select 'Data',
          left(right([FileName],charindex('\',reverse([FileName]))-1),
               charindex('.',right([FileName],
               charindex('\',reverse([FileName]))-1))-1),
          CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
          CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
          (CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
          -CAST(((UsedExtents*64)/1024.00) as numeric(9,2)))
   from   #data
   
   union all
   
   select 'Log',dbname+'_log',LogSize,
          ((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize)
   from   #log
   where  dbname = db_name()
   
   select FileType,[Name],Total,Used,[Free] 
   from #results order by FileType
   
   drop table #data
   drop table #log
   drop table #results

   return
end
else
begin

   /* Get data file(s) size */
   declare dcur cursor local fast_forward
   for
   select CATALOG_NAME 
   from INFORMATION_SCHEMA.SCHEMATA
   
   open dcur

   fetch next from dcur into @db

   while @@fetch_status=0
   begin

           set @cmd = 'use ' + @db + ' DBCC showfilestats'
           insert #data
           exec(@cmd) 

           insert #results(FileType,[Name],Total,Used,[Free],dbname)
           select 'Data',
                   left(right([FileName],charindex('\',reverse([FileName]))-1),
                        charindex('.',right([FileName],
                        charindex('\',reverse([FileName]))-1))-1),
                   CAST(((TotalExtents*64)/1024.00) as numeric(9,2)),
                   CAST(((UsedExtents*64)/1024.00) as numeric(9,2)),
                   (CAST(((TotalExtents*64)/1024.00) as numeric(9,2))
                   -CAST(((UsedExtents*64)/1024.00) as numeric(9,2))),
                   @db
           from   #data

           delete #data

           fetch next from dcur into @db

   end
   close dcur
   deallocate dcur

   /* Get log file(s) size */
   insert #log
   exec('dbcc sqlperf(logspace)')

   insert #results(FileType,[Name],Total,Used,[Free],dbname)
   select 'Log',dbname+'_log',LogSize,
          ((LogUsed/100)*LogSize),LogSize-((LogUsed/100)*LogSize),
          dbname
   from   #log
   

   select dbname,FileType,[Name],Total,Used,[Free]
   from #results order by dbname,FileType

   drop table #data
   drop table #log
   drop table #results

   return   

end