Credits:
Author: Unknown
Date: Sep 2009
Description
Returns the Database Size and file size on disk for all databases on a server.
At first glance, when preparing this code for publishing, I thought it could be better written using the sys.master_files table directly without the need for a temporary stored proc/dynamic SQL. However, it appears that the FILEPROPERTY TSQL function only operates in the context of the curent database and therefore requires a USE statement and a loop. This is not immediately obvious from BOL.
Ideally, I'd do without the cursor for performance reasons, but in this case, it's not a high use piece of code, so I've left it as originally intended.
Code
|
CREATE PROCEDURE #uspGetFileStats
@dbName sysname
AS
DECLARE @sqlCmd NVARCHAR(max)
SELECT @sqlCmd=
'Use '
+ @dbName+'; '
+ 'SELECT '''
+ @dbName
+ ''' [DBName]'
+ ',sum(ROUND((cast ([size] as numeric)*8/1024),2)) [FileSizeMb] '
+ ',sum(CASE '
+ 'WHEN FILEPROPERTY([name], ''SpaceUsed'') IS NULL THEN 0 '
+ 'ELSE '
+ 'ROUND(CAST (FILEPROPERTY([name], ''SpaceUsed'')as numeric)*8/1024,2) '
+ 'END) [UsedMb] '
+ ',CASE WHEN type = 0 THEN ''DATABASE'' WHEN type =1 THEN ''LOG'' WHEN type=4 THEN ''FULLTEXT'' END [FileType] '
+ 'FROM [sys].[database_files] '
+ 'GROUP BY type'
EXECUTE (@sqlCmd)
GO
DECLARE databaseNamesArr CURSOR FAST_FORWARD READ_ONLY
FOR
SELECT [name]
FROM sys.databases
ORDER BY [name] ASC
DECLARE @dbName NVARCHAR(128)
DECLARE @tFileStats TABLE ([DBName] SYSNAME,[FileSizeMb] NUMERIC(18,2),[UsedSpaceMb]NUMERIC(18,2),[FileType] NVARCHAR (60))
OPEN databaseNamesArr
FETCH NEXT
FROM databaseNamesArr
INTO @dbName
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @tFileStats
(
[DBName]
, [FileSizeMb]
, [UsedSpaceMb]
, [FileType]
)
EXECUTE #uspGetFileStats @dbName
FETCH NEXT
FROM databaseNamesArr
INTO @dbName
END
CLOSE databaseNamesArr
DEALLOCATE databaseNamesArr
SELECT [DBName]
,[UsedSpaceMb]
,[FileSizeMb]
,[FileType]
FROM @tFileStats
ORDER BY [DBName], [FileType]
GO
DROP PROCEDURE #uspGetFileStats
|
 Updating... Andy Hughes, Jun 7, 2012, 10:54 AM
|
