Script to get all Database Size occupied at file system level

Leave a comment (1) Go to comments

I have already published an Article to represent, Database size Growth chart report (graphically), in my previous post.

Today, I am going to post a small script to get current database size of all databases.

-- ***********************************************************************************/
-- Description  :    Retrieve Database Current size on OS
--                    Script that lists all database and occupied space at OS level
-- Compatibility  :     2005 +
-- ***********************************************************************************/
SET NOCOUNT ON
DECLARE @counter SMALLINT
DECLARE @counter1 SMALLINT
DECLARE @dbname VARCHAR(100)
DECLARE @size INT
DECLARE @size1 DECIMAL(15,2)
SET @size1=0.0

SELECT @counter=MAX(dbid) FROM master..sysdatabases
IF EXISTS(SELECT name FROM sysobjects WHERE name='sizeinfo')
DROP TABLE sizeinfo
CREATE TABLE sizeinfo(fileid SMALLINT, filesize DECIMAL(15,2), filename VARCHAR(1000))
WHILE @counter > 0
BEGIN
 SELECT @dbname=name FROM master..sysdatabases WHERE dbid=@counter
 TRUNCATE TABLE sizeinfo
 EXEC ('INSERT INTO sizeinfo SELECT fileid,size,filename FROM '+ @dbname +'..SYSFILES')
 SELECT @counter1=MAX(fileid) FROM sizeinfo
 WHILE @counter1>0
 BEGIN
  SELECT @size=filesize FROM sizeinfo WHERE fileid=@counter1
  SET @size1=@size1+@size
  SET @counter1=@counter1-1
 END
 SET @counter=@counter-1
 SELECT @dbname AS DBNAME,CAST(((@size1)*0.0078125) AS DECIMAL(15,2)) AS [DBSIZE(MB)]
 SET @size1=0.0
END
SET NOCOUNT OFF

OUTPUT


Script to get all Database Size occupied at file system level SQL Script that list total database size on server Size of all databases on SQL server script to get database size Script to get all Database space script to get all database size How to know SQL server database size database size

Related Post

Tags : Script to get all Database space, database size, script to get database size, script to get all database size,How to know SQL server database size,Size of all databases on SQL server,SQL Script that list total database size on server

EOF - Script to get all Database Size occupied at file system level, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

1 Comments.

  1. sp_helpdb gives the same result..

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.