Eirikur Eiriksson (1/24/2016)

---

Here is an alternative method for collecting this (almost the same) information in a single dynamic sql query.

😎

DECLARE @SQL_STR NVARCHAR(MAX) = N'';

DECLARE @OUTER_SQL NVARCHAR(MAX) = N'

;WITH FILE_INFO AS

(

SELECT

SDB.database_id

,QUOTENAME(SDB.name) AS DATABASE_NAME

,STUFF(

(

SELECT DISTINCT

NCHAR(44) + SUBSTRING(SSMF.physical_name,1,1)

FROM sys.master_files SSMF

WHERE SSMF.database_id = SDB.database_id

FOR XML PATH(''''),TYPE

).value(''(./text())[1]'',''NVARCHAR(64)'')

,1,1,N'''') AS DRIVE_LETTERS

,SDB.state_desc

,SDB.recovery_model_desc

,SUM(CASE WHEN SMF.type_desc = N''ROWS'' THEN CONVERT(NUMERIC(28,2),SMF.size * 0.0078125,0) ELSE 0 END) AS DB_SIZE

,SUM(CASE WHEN SMF.type_desc = N''LOG'' THEN CONVERT(NUMERIC(28,2),SMF.size * 0.0078125,0) ELSE 0 END) AS LOG_SIZE

,SUM(CASE WHEN SMF.type_desc = N''ROWS'' THEN

CASE

WHEN SMF.max_size IN (268435456,-1) THEN -1

ELSE CONVERT(NUMERIC(28,2),SMF.max_size * 0.0078125,0)

END

ELSE 0

END) AS DB_MAX_SIZE

,SUM(CASE WHEN SMF.type_desc = N''LOG'' THEN

CASE

WHEN SMF.max_size IN (268435456,-1) THEN -1

ELSE CONVERT(NUMERIC(28,2),SMF.max_size * 0.0078125,0)

END

ELSE 0

END) AS LOG_MAX_SIZE

FROM sys.databases SDB

INNER JOIN sys.master_files SMF

ON SDB.database_id = SMF.database_id

GROUP BY SDB.database_id

,SDB.name

,SDB.state_desc

,SDB.recovery_model_desc

)

SELECT

FI.database_id

,FI.DATABASE_NAME

,FI.DRIVE_LETTERS

,FI.state_desc

,FI.recovery_model_desc

,FI.DB_SIZE

,FI.LOG_SIZE

,FI.DB_MAX_SIZE

,FI.LOG_MAX_SIZE

,XINF.SYS_TBL_TOTAL

,XINF.SYS_TBL_USED

,XINF.SYS_TBL_DATA

,XINF.INT_TBL_TOTAL

,XINF.INT_TBL_USED

,XINF.INT_TBL_DATA

,XINF.USR_TBL_TOT_IRD

,XINF.USR_TBL_TOT_LOB

,XINF.USR_TBL_TOT_ROD

,XINF.USR_TBL_TOT_DRP

,XINF.USR_TBL_USED_IRD

,XINF.USR_TBL_USED_LOB

,XINF.USR_TBL_USED_ROD

,XINF.USR_TBL_USED_DRP

,XINF.USR_TBL_DPG_IRD

,XINF.USR_TBL_DPG_LOB

,XINF.USR_TBL_DPG_ROD

,XINF.USR_TBL_DPG_DRP

FROM FILE_INFO FI

LEFT OUTER JOIN

(

{{@SQL_STR}}

) AS XINF

ON FI.DATABASE_NAME = XINF.DATABASE_NAME

ORDER BY FI.DATABASE_NAME;';

DECLARE @DB_QRY NVARCHAR(MAX) = N'

UNION ALL

SELECT

N''{{@DBNAME}}'' AS DATABASE_NAME --OBJECT_NAME(SIDX.object_id)

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.total_pages ELSE 0 END ) AS SYS_TBL_TOTAL

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.used_pages ELSE 0 END ) AS SYS_TBL_USED

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''SYSTEM_TABLE'' THEN SAU.data_pages ELSE 0 END ) AS SYS_TBL_DATA

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.total_pages ELSE 0 END ) AS INT_TBL_TOTAL

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.used_pages ELSE 0 END ) AS INT_TBL_USED

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''INTERNAL_TABLE'' THEN SAU.data_pages ELSE 0 END ) AS INT_TBL_DATA

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_IRD

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_LOB

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_ROD

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.total_pages ELSE 0 END ) AS USR_TBL_TOT_DRP

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_IRD

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_LOB

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_ROD

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.used_pages ELSE 0 END ) AS USR_TBL_USED_DRP

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''IN_ROW_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_IRD

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''LOB_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_LOB

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''ROW_OVERFLOW_DATA'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_ROD

,SUM(CASE WHEN ISNULL(SOBJ.type_desc,SITT.type_desc) = N''USER_TABLE'' AND SAU.type_desc = N''DROPPED'' THEN SAU.data_pages ELSE 0 END ) AS USR_TBL_DPG_DRP

FROM {{@DBNAME}}.sys.indexes SIDX WITH (NOLOCK)

INNER JOIN {{@DBNAME}}.sys.partitions SPRT WITH (NOLOCK)

ON SIDX.object_id = SPRT.object_id

AND SIDX.index_id = SPRT.index_id

INNER JOIN {{@DBNAME}}.sys.allocation_units SAU WITH (NOLOCK)

ON SAU.container_id = CASE

WHEN SAU.type = 2 THEN SPRT.partition_id

ELSE SPRT.hobt_id

END

LEFT OUTER JOIN {{@DBNAME}}.sys.internal_tables SITT WITH (NOLOCK)

ON SIDX.object_id = SITT.object_id

LEFT OUTER JOIN {{@DBNAME}}.sys.objects SOBJ WITH (NOLOCK)

ON SIDX.object_id = SOBJ.object_id

';

SELECT @SQL_STR =

STUFF(

(

SELECT

REPLACE(@DB_QRY,N'{{@DBNAME}}', QUOTENAME(SDB.name))

FROM sys.databases SDB

WHERE SDB.state_desc = N'ONLINE'

FOR XML PATH(''),TYPE

).value('(./text())[1]','NVARCHAR(MAX)'),1,11,'');

SELECT @OUTER_SQL = REPLACE(@OUTER_SQL,N'{{@SQL_STR}}',@SQL_STR);

--SELECT @OUTER_SQL

EXEC (@OUTER_SQL);