Script to get All database file sizes, used and unused space

--
-- Author: Suleman Mohammed
-- Blog: www.sqlish.com
-- Create date: 29-05-2012
-- Description:

--Sproc to check data and log files sizes, usedspace and
--unused space

--Usage : To database size of a particular database
--
-- EXECUTE uspCheckDBSize
-- @dbname = <DatabaseName>
-- GO
--
--Usage : To database size of all databases
--
-- EXECUTE uspCheckDBSize
-- GO
--
--========================================================
--
-- Version Control 1.0 – 29-05-2012 Created Sproc – SM
-- Version Control 2.0 - 12-11-2013 Added Logic to get sizes of all database - SM
 --========================================================

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspCheckDBSize]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspCheckDBSize]
GO
CREATE PROCEDURE uspCheckDBSize
 @dbname SYSNAME = NULL
AS
BEGIN
 DECLARE @cmd NVARCHAR(4000)

CREATE TABLE #dbsizes
 (
 DatabaseName SYSNAME,
 DBFileName SYSNAME,
 FileSizeMB NUMERIC(10,2),
 UsedSpaceMB NUMERIC(10,2),
 UnusedSpaceMB NUMERIC(10,2),
 FileType SYSNAME
 )
 IF @dbname IS NULL
 BEGIN
 SET @dbname='?'
 END

&nbsp;

SET @cmd=N'USE '+@dbname+N';
 SELECT
 DB_NAME() AS [DatabaseName],
 [DBFileName] = ISNULL(a.name, ''*** Total size of the database ***''),
 [FileSizeMB] = CONVERT(NUMERIC(10, 2), SUM(ROUND(a.size / 128., 2))) ,
 [UsedSpaceMB] = CONVERT(NUMERIC(10, 2), SUM(ROUND(FILEPROPERTY(a.name,
 ''SpaceUsed'')
 / 128., 2))) ,
 [UnusedSpaceMB] = CONVERT(NUMERIC(10, 2), SUM(ROUND(( a.size
 - FILEPROPERTY(a.name,
 ''SpaceUsed'') )
 / 128., 2))) ,
 [Type] = CASE WHEN a.groupid IS NULL THEN '' ''
 WHEN a.groupid = 0 THEN ''Log''
 ELSE ''Data''
 END
 FROM sysfiles a
 GROUP BY groupid ,
 a.name
 WITH ROLLUP
 HAVING a.groupid IS NULL
 OR a.name IS NOT NULL
 ORDER BY CASE WHEN a.groupid IS NULL THEN 99
 WHEN a.groupid = 0 THEN 0
 ELSE 1
 END ,
 a.groupid ,
 CASE WHEN a.name IS NULL THEN 99
 ELSE 0
 END ,
 a.name'

 PRINT @CMD

IF @dbname = '?'
 BEGIN
 INSERT INTO #dbsizes
 EXECUTE sp_msforeachdb @CMD
 END
 ELSE
 BEGIN
 INSERT INTO #dbsizes
 EXECUTE sp_executesql @statement=@cmd
 END

SELECT * FROM #dbsizes

DROP TABLE #dbsizes

END
GO
This entry was posted in SQL Server, T-SQL Scripts and tagged , , , . Bookmark the permalink.

Leave a Reply