use master
go

if not exists (select * from information_schema.routines where routine_name = 'sp_EstimateAllDBBackupSize')
    exec ('create proc dbo.sp_EstimateAllDBBackupSize as select ''stub version, to be replaced''')
go



alter proc sp_EstimateAllDBBackupSize
as
/*********************************************************************************************
sp_EstimateAllDBBackupSize (c) 2015 SQLschool.gr - Antonios Chatzipavlis

Version History
1.00    Sep 19, 2015
1.01    Sep 22, 2015 fix the overflow and offline databases errors

Feedback: info@sqlschool.gr

License: 
    sp_EstimateAllDBBackupSize is free to download and use for personal, educational, 
    and internal corporate purposes, provided that this header is preserved. 
*********************************************************************************************/
begin
    set nocount on;

    declare @dbname sysname;
    declare @query nvarchar(1000);
    declare @stm nvarchar(1000);

    set @query = 'use ?
    insert into #backupsize
    select      db_name() 
            , convert(decimal(15,2), sum(size) * 8192.0 / 1048576.0) 
            , convert(decimal(15,2), sum(fileproperty(name,''spaceused'')) * 8192.0 / 1048576.0) 
    from sys.database_files;'

    if ( object_id('tempdb..#backupsize') is not null )
    begin
        drop table #backupsize;
    end    

    create table  #backupsize
    (
        database_name sysname
    ,    total_db_size_mb decimal(15, 2)
    ,    estimated_backup_size_mb decimal(15, 2)
    );
    
    declare dblist cursor local fast_forward for 
            select name from sys.databases 
            where state=0 
            order by name;

    open dblist;

    fetch next from dblist into @dbname;
    while (@@fetch_status <> -1)
    begin
        if (@@fetch_status <> -2)
        begin
            set @stm =replace(@query,'?',QUOTENAME(@dbname));
            execute(@stm);
        end
        fetch next from dblist into @dbname;
    end

    close dblist;
    deallocate dblist;

    select * from #backupsize;
end
go