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