use master

 go

 create procedure dbo.proc_getdbspaceused

 as

 begin

 set nocount on

 create table #dbsize(

      database_id int

     ,database_name nvarchar(1024)

     ,size_kb bigint

     ,space_available_kb bigint

     ,reserved_kb bigint

     ,data_kb bigint

     ,index_kb bigint

     ,unused_kb bigint

 )  

 declare @database_id int

 declare @name nvarchar(1024)

 declare @sql nvarchar(max)

 declare cur cursor for select database_id,name from master.sys.databases order by database_id

 open cur

 fetch next from cur into @database_id,@name

 while @@fetch_status=0

 begin

 set @sql =N'

 insert into #dbsize

 select

     database_id = '+CONVERT(nvarchar(10),@database_id)+',

     database_name = '''+@name+''',

     size_kb = ((dbsize + logsize) * 8192 / 1024),

     space_available_kb = (case when dbsize >= reservedpages then ((dbsize-reservedpages)* 8192/ 1024) else 0 end),

     reserved_kb = (reservedpages * 8192 / 1024),

     data_kb = (pages * 8192.0 / 1024),

     index_kb = ((usedpages - pages)  * 8192 / 1024),

     unused_kb = ((reservedpages - usedpages)  * 8192 / 1024)

 from(

     select

     dbsize = sum(convert(bigint,case when status & 64 = 0 then size else 0 end)) ,

     logsize = sum(convert(bigint,case when status & 64 <> 0 then size else 0 end))

     from ['+@name+'].dbo.sysfiles

 ) t1,(

     select

     reservedpages = sum(a.total_pages),

     usedpages = sum(a.used_pages),

     pages = sum(

         CASE

          When it.internal_type IN (202,204,211,212,213,214,215,216) Then 0

          When a.type <> 1 Then a.used_pages

          When p.index_id < 2 Then a.data_pages Else 0

         END )

     from ['+@name+'].sys.partitions p

     inner join ['+@name+'].sys.allocation_units a on p.partition_id = a.container_id

     left  join ['+@name+'].sys.internal_tables it on p.object_id = it.object_id

 )t2'

 exec(@sql)

 fetch next from cur into @database_id,@name

 end

 close cur

 deallocate cur  

 select * from #dbsize

 end

 go  

 exec master.dbo.proc_getdbspaceused  

 --  drop table #dbsize

 --  drop table #logsize

 create table #dbsize(

      database_id int

     ,database_name nvarchar(1024)

     ,size_kb bigint

     ,space_available_kb bigint

     ,reserved_kb bigint

     ,data_kb bigint

     ,index_kb bigint

     ,unused_kb bigint

 )

 go

 create table #logsize(

     database_name nvarchar(1024)

     ,LogSize_MB decimal(24,8)

     ,LogSpaceUsed decimal(14,8)

     ,Status tinyint

 )

 go

 insert into #dbsize exec master.dbo.proc_getdbspaceused

 go

 insert into #logsize exec('DBCC sqlperf(logspace)')

 go

 select t0.database_id,t0.database_name

 ,t0.size_kb/1024 as [数据库大小MB]

 ,t0.space_available_kb/1024 as [可用空间MB]

 ,LogSize_MB as [日志大小MB]

 ,LogSpaceUsed as [日志已使用%]

 ,t1.compatibility_level as [兼容级别]

 ,t1.collation_name as [校对规则]

 ,t1.recovery_model_desc as [恢复模式]

 ,(case t3.type/*btype*/ when 'D' then '完整' when 'I' then '差异' when 'L' then '日志' end)as [备份模式]

 ,最近备份时间,备份大小MB,备份耗时Min,备份目录

 from #dbsize t0

 inner join master.sys.databases t1 on t0.database_name=t1.name and t1.state_desc='ONLINE'

 inner join #logsize t2 on t0.database_name=t2.database_name

 --LEFT JOIN (select btype from (values('D'),('I'),('L')) as btype(btype)) t4 on 1=1

 left join (

     select a.database_name,a.type

     ,CONVERT(varchar(30),a.backup_start_date,120) AS 最近备份时间

     ,convert(decimal(18,2),compressed_backup_size/1024/1024.)  as 备份大小MB

     ,DATEDIFF(MINUTE,backup_start_date,backup_finish_date) as 备份耗时Min

     ,REVERSE(stuff(REVERSE(physical_device_name),1,CHARINDEX('\',REVERSE(physical_device_name))-1,'')) as 备份目录

     from msdb.dbo.backupset a

     inner join (

         select database_name,type,MAX(backup_start_date) as latest_backup_date

         from msdb.dbo.backupset

         group by database_name,type

     )b on a.database_name=b.database_name and a.type=b.type and a.backup_start_date=b.latest_backup_date

     left join msdb.dbo.backupmediafamily c on a.media_set_id=c.media_set_id

 )t3 on t0.database_name=t3.database_name --and t2.btype=t3.type

 go