SET NOCOUNT ON

declare @dbname sysname 

declare @exec_stmt nvarchar(625)
declare @showdev	bit
declare @name          sysname
declare @cmd	nvarchar(279)
declare @low nvarchar(11)
declare @dbdesc nvarchar(4000)	/* the total description for the db */
declare @propdesc varchar(40)
declare @rowct smallint,@curRow smallint
declare @cmd1 nvarchar(4000)
declare @xname varchar(200),@xfileID varchar(500),@xfilename varchar(3000),
		@xfilegroup varchar(300),@xsize varchar(200),@xmaxsize varchar(200),
		@xgrowth varchar(100),@xusage varchar(150),@xdbname varchar(100)

create table #tempfile (xname varchar(200),xfileID varchar(500),xfilename varchar(3000),
		xfilegroup varchar(300),xsize varchar(200),xmaxsize varchar(200),
		xgrowth varchar(100),xusage varchar(150))

create table #spdbdesc
(
	dbname sysname,
	owner sysname,
	created varchar(11),
	dbid	smallint,
	dbdesc	nvarchar(4000)	null,
	dbsize	varchar(13) null,
	cmptlevel tinyint,
	[name] varchar(500),
	[filename] varchar(1000),
	[filegroup]varchar(400),
	[size]	varchar(100),
	[maxsize] varchar(300),
	growth	varchar(300),
	usage varchar(100)

)

if @dbname is null
	select @showdev = 0
else select @showdev = 1

if not exists (select * from master.dbo.sysdatabases
	where (@dbname is null or name = @dbname))
	begin
		raiserror(15010,-1,-1,@dbname)
	end

select @low = convert(varchar(11),low) from master.dbo.spt_values
			where type = N'E' and number = 1

insert into #spdbdesc (dbname, owner, created, dbid, cmptlevel)
		select name, suser_sname(sid), convert(nvarchar(11), crdate),
			dbid, cmptlevel from master.dbo.sysdatabases
			where (@dbname is null or @dbname=[name])

select @low = convert(varchar(11),low) from master.dbo.spt_values
			where type = N'E' and number = 1

declare ms_crs_c1 cursor for
	select db_name (dbid) from #spdbdesc
open ms_crs_c1
fetch ms_crs_c1 into @name
while @@fetch_status >= 0
begin
	if (has_dbaccess(@name) <> 1)
	begin
	  delete #spdbdesc where current of ms_crs_c1
	  raiserror(15622,-1,-1, @name)
	end
	else
		begin
			/* Insert row for each database */
			select @exec_stmt = 'update #spdbdesc set dbsize = (select str(convert(dec(15),sum(size))* ' 
				+ @low + '/ 1048576,10,2)+ N'' MB'' from '
 				+ quotename(@name, N'[') + N'.dbo.sysfiles) WHERE current of ms_crs_c1'

			execute (@exec_stmt)
		end
	fetch ms_crs_c1 into @name
end
deallocate ms_crs_c1

declare @curdbid smallint	

select @curdbid = min(dbid) from #spdbdesc


while @curdbid IS NOT NULL
begin
	set @name = db_name(@curdbid)

	SELECT @dbdesc = 'Status=' + convert(sysname,DatabasePropertyEx(@name,'Status'))
	SELECT @dbdesc = coalesce(@dbdesc + ',' ,'') + ' Updateability=' + convert(sysname,DatabasePropertyEx(@name,'Updateability'))
	SELECT @dbdesc = coalesce(@dbdesc + ',' ,'') +  ' UserAccess=' + convert(sysname,DatabasePropertyEx(@name,'UserAccess'))
	SELECT @dbdesc = coalesce(@dbdesc + ',','') +  ' Recovery=' + convert(sysname,DatabasePropertyEx(@name,'Recovery'))
	SELECT @dbdesc = coalesce(@dbdesc + ',' ,'') +  ' Version=' + convert(sysname,DatabasePropertyEx(@name,'Version'))

	IF DatabaseProperty(@name, 'IsShutdown') = 0
	BEGIN
		SELECT @dbdesc = coalesce(@dbdesc + ',' ,'') +  ' Collation=' + convert(sysname,DatabasePropertyEx(@name,'Collation'))
		SELECT @dbdesc = coalesce(@dbdesc + ',','') +  ' SQLSortOrder=' + convert(sysname,DatabasePropertyEx(@name,'SQLSortOrder'))
	END

	IF DatabasePropertyEx(@name,'IsAutoClose') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsAutoClose'
	IF DatabasePropertyEx(@name,'IsAutoShrink') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',','') + 'IsAutoShrink'
	IF DatabasePropertyEx(@name,'IsInStandby') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsInStandby'
	IF DatabasePropertyEx(@name,'IsTornPageDetectionEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsTornPageDetectionEnabled'
	IF DatabasePropertyEx(@name,'IsAnsiNullDefault') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsAnsiNullDefault'
	IF DatabasePropertyEx(@name,'IsAnsiNullsEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsAnsiNullsEnabled'
	IF DatabasePropertyEx(@name,'IsAnsiPaddingEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsAnsiPaddingEnabled'
	IF DatabasePropertyEx(@name,'IsAnsiWarningsEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsAnsiWarningsEnabled'
	IF DatabasePropertyEx(@name,'IsArithmeticAbortEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsArithmeticAbortEnabled'
	IF DatabasePropertyEx(@name,'IsAutoCreateStatistics') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsAutoCreateStatistics'
	IF DatabasePropertyEx(@name,'IsAutoUpdateStatistics') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsAutoUpdateStatistics'
	IF DatabasePropertyEx(@name,'IsCloseCursorsOnCommitEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsCloseCursorsOnCommitEnabled'
	IF DatabasePropertyEx(@name,'IsFullTextEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsFullTextEnabled'
	IF DatabasePropertyEx(@name,'IsLocalCursorsDefault') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',','') + 'IsLocalCursorsDefault'
	IF DatabasePropertyEx(@name,'IsNullConcat') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsNullConcat'
	IF DatabasePropertyEx(@name,'IsNumericRoundAbortEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsNumericRoundAbortEnabled'
	IF DatabasePropertyEx(@name,'IsQuotedIdentifiersEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsQuotedIdentifiersEnabled'
	IF DatabasePropertyEx(@name,'IsRecursiveTriggersEnabled') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsRecursiveTriggersEnabled'
	IF DatabasePropertyEx(@name,'IsMergePublished') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsMergePublished'
	IF DatabasePropertyEx(@name,'IsPublished') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',','') + 'IsPublished'
	IF DatabasePropertyEx(@name,'IsSubscribed') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',','') + 'IsSubscribed'
	IF DatabasePropertyEx(@name,'IsSyncWithBackup') = 1
		SELECT @dbdesc =coalesce(@dbdesc + ',' ,'') + 'IsSyncWithBackup'

	update #spdbdesc set dbdesc = @dbdesc where dbid = @curdbid

	truncate table #tempfile
	set @cmd1='use [' + @name + ']; insert into #tempFile exec sp_helpfile'
	exec sp_executesql @cmd1
	update #tempfile set xfileID=@name

	set @xname=null
	set @xfilename=null
	set @xfilegroup=null
	set @xsize=null
	set @xmaxsize=null
	set @xgrowth =null
	set @xusage=null
	while exists(select * from #tempfile)
	begin
		set rowcount 1
		select @xname=coalesce(@xname + ',' ,'') + rtrim(xname) ,@xdbname=rtrim(xname),
			@xfilename=coalesce(@xfilename + ',' ,'')+ rtrim(xfilename),
			@xfilegroup=coalesce(@xfilegroup + ',' ,'')+ rtrim(coalesce(xfilegroup,'Null')),
			@xsize= coalesce( @xsize + ',' ,'')+ rtrim(xsize),
			@xmaxsize= coalesce(@xmaxsize + ',' ,'')+ rtrim(xmaxsize),
			@xgrowth= coalesce(@xgrowth + ',','') + rtrim(xgrowth),
			@xusage=coalesce(@xusage + ',' ,'')+ rtrim(xusage)
			from #tempfile
		
		set rowcount 0

		delete from #tempfile where xname=@xdbname
	end
	update #spdbdesc
		set name=@xname,filename=@xfilename,filegroup=@xfilegroup,
		size=@xsize,maxsize=@xmaxsize,growth=@xgrowth,usage=@xusage
		where dbname=@name
	select @curdbid = min(dbid) from #spdbdesc where dbid > @curdbid
end

select name = dbname ,
	db_size = dbsize,
	owner = owner,
	dbid = dbid,
	created = created,
	status = dbdesc,
    	compatibility_level = cmptlevel,
	name,filename,filegroup,size,maxsize,growth,usage
from  #spdbdesc 
order by dbname

drop table #tempfile
drop table #spdbdesc

set nocount off