/*

Author 				: Kuldip Rindani
Create Date			: 01-Jan-2009
Purpose				: Display Disk space usage for all user created Database in Sql Server 2000 Machine

Modification History		:

1. Added Database names which should be ignored like Capabilities_Grid,  mssecurity.

*/

Set nocount on 

SET DEADLOCK_PRIORITY LOW
Declare @DatabaseName varchar(50)
Declare @LogSize float, @DbSize float, @DBTotalSize float, @TotalLogSize float, @TotalDbSize float,
	@TotalSize float, @DBPath varchar(255), @dbName varchar(255)

Create Table ##tempValue (groupid smallint, [name] nchar(128), [filename] nchar(260), TotalSize float)
Create Table #tmpTable (DatabasName varchar(50), DatabasePath varchar(255), [LogSize in MB] float, 
		[DbSize in MB] float, [DBTotalSize in MB] float)


Set @TotalLogSize = 0
Set @TotalDbSize = 0
Set @TotalSize = 0

Declare C1 cursor For select  [name] from master..sysdatabases with (nolock) where 
	[name] not in ('master', 'model', 'msdb', 'Capabilities_Grid', 'CDWINNT', 'dbatools', 
		'alert_db','distribution', 'gdmgaudit', 'mssecurity')  Order By [name] 
Open C1
Fetch Next From  C1 InTo @DatabaseName
While @@Fetch_Status <>-1
Begin

	Set @LogSize =0
	Set @DbSize = 0
	Set @DBTotalSize = 0	
	
	Execute ('Insert Into ##tempValue select Groupid, [name], [filename], cast((Size*8)/1024 as float) as [size] from  ' + @DatabaseName + '..sysfiles with (nolock)  ')
	Select @dbName= [name], @DBPath = [filename], @DbSize = [TotalSize] From ##tempValue where groupid = 1
	Select @LogSize = [TotalSize] From ##tempValue where groupid = 0
	Delete From ##tempValue	
	
	Set @DBTotalSize = @DbSize + @LogSize
	Set @TotalLogSize = @TotalLogSize + @LogSize
	Set @TotalDbSize = @TotalDbSize + @DbSize
	Set @TotalSize = @TotalSize + @DBTotalSize


	Insert Into #tmpTable Values(@DatabaseName, @DBPath, @LogSize, @DbSize, @DBTotalSize)
	Fetch Next From  C1 InTo @DatabaseName
End
Close C1
Deallocate C1

Insert Into #tmpTable Values('Total of All Country', 'NA', @TotalLogSize, @TotalDbSize, @TotalSize)

Select * From #tmpTable Order By [DBTotalSize in MB] desc, [DbSize in MB] desc, [LogSize in MB] desc, DatabasName

drop table #tmpTable
drop table ##tempValue



/*select * from master..syslockinfo
select * from master..sysdepends
select * from master..sysprocesses with (nolock)
*/

--select * from master..sysdatabases
--select  * from sysfiles
--select  (Size*8)/1024, [name], filename, * from sysfiles