모든 DB의 파일 크기를 알아내는 스크립트

> Link : http://databaser.net/

이재학님의 사이트(www.databaser.net)에서 찾은 스크립트입니다..

참고하세요~~~^^

 

 

CREATE PROC USP_SELECT_DBSize

AS

BEGIN

             CREATE TABLE #temp (

             DBName varchar(255)

             , Device_Name varchar(25)

             , Device_Size varchar(13)

             , Path varchar(100)

             , Filegroup varchar(100)

             )

 

 DECLARE @dbname varchar(50)

 DECLARE @command varchar(255)

 

 DECLARE dbname_cursor CURSOR FOR SELECT name from master..sysdatabases 

         where name not in ('northwind', 'pubs')

 

 OPEN dbname_cursor

 FETCH next FROM dbname_cursor into @dbname

 

 WHILE @@fetch_status = 0

 BEGIN

  SELECT @command = 'USE ' + @dbname + ' select ' + '''' +  @dbname + '''' + ',' +

      'convert(varchar(25),f.name) ''Device Name'','+ 

      'convert(varchar(10),size/128) + '' MB'' ''Device Size'','+ 

      'convert(varchar(100),f.filename) Path,'+ 

      'convert(varchar(15),filegroup_name(groupid)) Filegroup from sysfiles f' 

  INSERT #temp EXEC (@command)

 FETCH NEXT FROM dbname_cursor INTO @dbname

 END

 

 SELECT *

 FROM #temp

 ORDER BY DBName

 

 DROP TABLE #temp

 

 CLOSE dbname_cursor

 DEALLOCATE dbname_cursor

END

GO