Databases Sizes and Current DB Table Size

The following query will create a store proc SP_PH_DATABASE_USAGE, which will give the DataBase Sizes and Table Sizes for the current DataBase.

USE MASTER
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'SP_PH_DATABASE_USAGE' AND XTYPE = 'P')
DROP PROC SP_PH_DATABASE_USAGE 
GO


CREATE PROCEDURE SP_PH_DATABASE_USAGE AS
SET NOCOUNT ON
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORDB' )
DROP TABLE ##DATASPACEFORDB
IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORDB' )
DROP TABLE ##TEMPFORDB




CREATE TABLE ##DATASPACEFORDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORDB
 EXEC( 'DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORDB FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORDB 
SELECT '50','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID




IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##DATASPACEFORTEMPDB' )
DROP TABLE ##DATASPACEFORTEMPDB


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TEMPFORTEMPDB' )
DROP TABLE ##TEMPFORTEMPDB


CREATE TABLE ##DATASPACEFORTEMPDB
 ( FILEID NUMERIC,
FILEGRP NUMERIC,
TOTEXT NUMERIC,
USDEXT NUMERIC,
   LFILENM VARCHAR( 100),
   PFILENM VARCHAR( 100)
   )
INSERT ##DATASPACEFORTEMPDB
 EXEC( 'USE TEMPDB DBCC SHOWFILESTATS WITH NO_INFOMSGS')


SELECT FILEID,GROUPNAME,(TOTEXT*64)/1024 AS 'TOTALSPACE_MB',(USDEXT*64)/1024 AS 'SPACEUSED_MB',LFILENM,PFILENM 
INTO ##TEMPFORTEMPDB FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID
ORDER BY GROUPNAME


INSERT INTO ##TEMPFORTEMPDB 
SELECT '100','TOTAL DATA FILE SIZE IN GB',SUM((TOTEXT*64)/1024)/1024 ,SUM((USDEXT*64)/1024)/1024  ,'',''
FROM ##DATASPACEFORTEMPDB WITH (NOLOCK), SYSFILEGROUPS  WITH (NOLOCK) WHERE FILEGRP = GROUPID


IF EXISTS( SELECT * FROM TEMPDB..SYSOBJECTS WHERE NAME = '##TMPLOGSPACE' )
DROP TABLE ##TMPLOGSPACE


CREATE TABLE ##TMPLOGSPACE 
(
DATABASENAME VARCHAR(100),
LOGSIZE VARCHAR(100),
LOGSPACEUSED VARCHAR(100),
STATUS VARCHAR(100)
)


INSERT INTO ##TMPLOGSPACE 
    EXEC ('DBCC SQLPERF ( LOGSPACE ) WITH NO_INFOMSGS')




SELECT '                        ' AS 'DATABASENAME',* INTO ##FINALTABLEFORDB  FROM ##TEMPFORDB WHERE 1=2


INSERT INTO ##FINALTABLEFORDB  
SELECT DB_NAME(),* FROM ##TEMPFORDB 


INSERT INTO ##FINALTABLEFORDB  
 SELECT 'TEMPDB',* FROM ##TEMPFORTEMPDB


INSERT INTO ##FINALTABLEFORDB  
SELECT DATABASENAME,101,'TOTAL LOG SIZE IN GB',CONVERT(NUMERIC(20),LOGSIZE)/1024 , (CONVERT(NUMERIC(20),((CONVERT(NUMERIC(20),LOGSPACEUSED)*LOGSIZE)/100)))/1024,'',''  
FROM ##TMPLOGSPACE WHERE DATABASENAME IN (DB_NAME(),'TEMPDB' )


SELECT UPPER(DATABASENAME),FILEID,GROUPNAME,CONVERT(NUMERIC(20),TOTALSPACE_MB) AS TOTALSPACE_MB,CONVERT(NUMERIC(20),SPACEUSED_MB) AS SPACEUSED_MB ,CONVERT(NUMERIC(20),(TOTALSPACE_MB-SPACEUSED_MB)) AS 'FREESPACE_MB' , LFILENM,PFILENM 
FROM ##FINALTABLEFORDB  ORDER BY 1,2


DROP TABLE ##DATASPACEFORDB, ##TEMPFORDB
DROP TABLE ##DATASPACEFORTEMPDB, ##TEMPFORTEMPDB
DROP TABLE ##TMPLOGSPACE, ##FINALTABLEFORDB


 --temp table for further querying
CREATE TABLE #temp(
rec_id int IDENTITY (1, 1),
table_name varchar(128),
nbr_of_rows int,
data_space decimal(15,2),
index_space decimal(15,2),
total_size decimal(15,2),
percent_of_db decimal(15,12),
db_size decimal(15,2))


-- Get all tables, names, and sizes
EXEC sp_msforeachtable @command1="insert into #temp(nbr_of_rows, data_space, index_space) exec sp_mstablespace '?'",
@command2="update #temp set table_name = '?' where rec_id = (select max(rec_id) from #temp)"


-- Set the total_size and total database size fields
UPDATE #temp
SET total_size = (data_space + index_space), db_size = (SELECT SUM(data_space + index_space) FROM #temp)


-- Set the percent of the total database size
UPDATE #temp
SET percent_of_db = (total_size/db_size) * 100


-- Get the data
SELECT *
FROM #temp
ORDER BY total_size DESC


-- Comment out the following line if you want to do further querying
DROP TABLE #temp


GO