Database Size and Free Size and Total Database Log Size and Free Size

--Procedure to find out Total Database Size and Free Size and Total Database Log Size and Free Size--

IF EXISTS (SELECT * FROM sysobjects WHERE id = object_id(N'[dbo].[sp_Datasize_alldatabases]') AND
OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp_Datasize_alldatabases]
GO


create procedure sp_Datasize_alldatabases
as
declare @cmd nvarchar(1024)
if exists (select * from tempdb..sysobjects where id = object_id(N'[tempdb]..[#tmplg]'))
drop table #tmplg
CREATE TABLE #tmplg
(
DBName varchar(32),
LogSize numeric(18,2),
LogSpaceUsed real,
Status int
)
SELECT @cmd = 'dbcc sqlperf (logspace)'
INSERT INTO #Tmplg EXECUTE (@cmd)

if exists (select * from tempdb..sysobjects where id = object_id(N'[tempdb]..[#tmp_data]'))
drop table #tmp_data
create table #tmp_data (
fileid int,
filegroup int,
totalextents numeric(18,2),
usedextents numeric(18,2),
name varchar(1024),
filename varchar(1024)
)

--declare @cmd varchar(500)

declare @db char( 40)
declare dbname scroll cursor
 for select DBName from #tmplg

open dbname
fetch next from dbname into @db
while @@fetch_status = 0
 begin

  select @cmd = 'use ' + rtrim( @db) + ' dbcc showfilestats'

truncate table #tmp_data
insert #tmp_data
exec( @cmd)

begin
alter table #tmp_data add dbname varchar(500)

declare @updatecmd varchar(500)
select @updatecmd = 'update #tmp_data set dbname=' + char(39) +@db +char(39)
exec(@updatecmd)
end

declare @logsize numeric(18,2)
declare @logspaceused real--numeric(18,2)
declare @logspaceused_per numeric(18,2)
select @logsize= logsize from #tmplg where dbname = @db
select @logspaceused = (logsize*logspaceused)/100.0
      from #tmplg where dbname = @db
select @logspaceused_per = logspaceused from #tmplg where dbname=@db

set @cmd = ''

declare @Growth_in_MB numeric(9,2)
declare @current_datasize numeric(9,2)
declare @previous_datasize numeric(9,2)
declare @current_logsize numeric(9,2)
DECLARE @CNT INT

select @current_datasize = sum(usedextents)*64/1024 from #tmp_data

SELECT @CNT=COUNT(*) FROM master..DBSTATS where id <(select max(id) from master..DBSTATS where dbname=@db) and dbname=@db

IF(@CNT<1)
    SELECT @PREVIOUS_DATASIZE=DATA_USED FROM MASTER..DBSTATS WHERE DBNAME=@db
ELSE
    select @previous_datasize = data_used from master..DBSTATS where id =(select max(id) from master..DBSTATS where dbname=@db) and dbname=@db

select @Growth_in_MB = @current_datasize-@previous_datasize


select @cmd = 'insert into master.dbo.DBSTATS' +
   '(DATA_SIZE,DATA_USED,[DATA_USED IN %],dbname,LOG_SIZE,LOG_USED,[LOG_USED IN %],Growth_in_MB)' +
    'select sum(totalextents)*64/1024, sum(usedextents)*64/1024,((100)*(sum(usedextents)*64/1024))/(sum(totalextents)*64/1024),'
    + char(39) + rtrim(@db)+ char(39) + ',' +
    cast(@logsize as varchar) + ',' + cast(@logspaceused as varchar) + ',' + cast(@logspaceused_per as varchar) + ',' + isnull(cast(@Growth_in_MB as varchar),0) +
 ' from #tmp_data where dbname=' + char(39)+@db+char(39)

exec sp_executesql @cmd

alter table #tmp_data drop column dbname

  fetch next from dbname into @db
end

close dbname
deallocate dbname

drop table #tmp_data
drop table #tmplg
select * from master.dbo.DBSTATS
--truncate table master.dbo.DBSTATS
--exec sp_Datasize_alldatabases
go