File space Alert for Databases

go

drop table #templist

-- =============================================

-- Declare and using a READ_ONLY cursor

-- =============================================

set nocount on

select [name] as dbname

, fileid, sf.groupid, grp=left([groupname],20), lname=left([name],20), size_mb=[size]/128 

  ,used_mb=FILEPROPERTY([name], 'SpaceUsed')/128

  ,file_growth=case when (sf.status&0x100000) > 0 then str(growth)+' %'

     else str(growth/128)+' mb' end

  ,max_mb = case when [maxsize]<0 then 'Unrestricted'

     else str([maxsize]/128) end

  ,phname=left(filename,70)  into #templist

  from 

   sysfiles sf left outer join  sysfilegroups sfg on sf.groupid=sfg.groupid

where 1 = 2

alter table  #templist add UpdateStatement varchar(512)

DECLARE Cur_SysDB CURSOR

READ_ONLY

FOR 

SELECT name FROM SYSDATABASES 

DECLARE @name varchar(40)

OPEN Cur_SysDB

FETCH NEXT FROM Cur_SysDB INTO @name

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

  exec ('insert into #templist 

  select ''' + @name + ''' as Dbnamename,  fileid, sf.groupid, grp=left([groupname],20), lname=left([name],20), size_mb=[size]/128 

  ,used_mb=FILEPROPERTY([name], ''SpaceUsed'')/128

  ,file_growth=case when (sf.status&0x100000) > 0 then str(growth) + '' %''

     else str(growth/128)+'' mb'' end

  ,max_mb = case when [maxsize]< 0 then ''Unrestricted''

     else str([maxsize]/128) end

  ,phname=left(filename,70),

    ''use [' + @name + '] ; update #templist set used_mb = FILEPROPERTY([name], ''''SpaceUsed'''')/128

from [' + @name + '].dbo.sysfiles sf left outer join  [' + @name + '].dbo.sysfilegroups sfg on sf.groupid=sfg.groupid 

  where  phname collate database_default = filename '' as updatestatement

  from ' + @name 

   + '.dbo.sysfiles sf left outer join  ' + @name + '.dbo.sysfilegroups sfg on sf.groupid=sfg.groupid   order by 1')

END

FETCH NEXT FROM Cur_SysDB INTO @name

END

CLOSE Cur_SysDB

DEALLOCATE Cur_SysDB

go

-- drop table #templist

DECLARE Cur_SysDBUpdate CURSOR

READ_ONLY

FOR 

SELECT distinct UpdateStatement  FROM #templist

DECLARE @updatestatement varchar(512)

OPEN Cur_SysDBUpdate

FETCH NEXT FROM Cur_SysDBUpdate INTO @updatestatement

WHILE (@@fetch_status <> -1)

BEGIN

IF (@@fetch_status <> -2)

BEGIN

      Exec (@updatestatement)

END

FETCH NEXT FROM Cur_SysDBUpdate INTO @updatestatement

END

CLOSE Cur_SysDBUpdate

DEALLOCATE Cur_SysDBUpdate

go

-- select dbname , case when used_mb > 0 then cast(used_mb as decimal(5,2)) / cast(size_mb as decimal(5,2) * 100 else 0.00 end from #templist

set nocount off

go

select dbname, grp, lname, size_mb, used_mb,cast( case when used_mb > 0 then 

convert(dec(29,2), used_mb) / cast(size_mb as decimal(29,2)) * 100 

else '0' end as decimal(5,2)) USagepercent , phname,file_growth, max_mb

  from #templist where dbname like 'tempd' and grp is not null

order by 4 desc