모든 DB의 파일 크기를 알아내는 스크립트
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