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
|