sp_spaceused2

-- www.qdpma.com/SQL/SqlScripts.html
-- comment 2018-10-04 
-- if the intent is to call this procedure frequently with any of the three optional parameters 
-- then consider replacing the first CTE with a temp table or table variable 
-- and using a statement level recompile to insert into the temp table: OPTION (RECOMPILE) 
-- 
-- update 2018-02-24 
-- update 2018-10-11: now using table variables in place of CTEs 
USE master -- skip this on Azure
GO 
IF EXISTS ( 
  SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('sp_spaceused2') 
) DROP procedure [dbo].sp_spaceused2 
GO 
CREATE PROCEDURE [dbo].[sp_spaceused2] @objname nvarchar(776) = NULL, @psid int = NULL, @minrow int = 0 
AS 
SET NOCOUNT ON 
DECLARE @objid int , @dbname sysname 
-- Check to see that the object names are local to the current database. 
select @dbname = parsename(@objname,3) 
if @dbname is null 
  select @dbname = db_name() 
else if @dbname <> db_name() 
begin 
  raiserror(15250, - 1,-1)  
return (1) 
end 
-- Check to see the the table exists and initialize @objid. 
select @objid = object_id(@objname) 
  
DECLARE @ClK TABLE( object_id int primary key , ClKey varchar(4000) ) 
;WITH j1 AS ( 
  SELECT j.object_id, j.index_id, j.key_ordinal, c.column_id, c.name,is_descending_key 
  FROM sys.index_columns j INNER JOIN sys.columns c ON c.object_id = j.object_id AND c.column_id = j.column_id 
) 
INSERT @ClK 
SELECT c.object_id , ISNULL(STUFF(( SELECT ', ' + name + CASE is_descending_key WHEN 1 THEN '-' ELSE '' END 
  FROM j1 WHERE j1.object_id = c.object_id AND j1.index_id = 1 AND j1.key_ordinal > 0 
  ORDER BY j1.key_ordinal FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'), 1,1,'') ,'') as ClKey 
FROM sys.indexes c WHERE c. index_id = 1 
  
DECLARE @c TABLE( otype varchar(2) , object_id int , Ord int , data_space_id int 
, [Rows] bigint , Reserved bigint , Used bigint , [Data] bigint 
, index2 bigint , index3 bigint , in_row_data bigint , lob bigint , ovrflw bigint 
, Cmpr int , Part int , Pop int , Ppz int , Cnt int 
, Clus int , IxCt int , XmlC int , SpaC int , CoSC int 
, ncs int , Uniq int , disa int , hypo int , filt int 
, PRIMARY KEY( object_id,data_space_id) ) 
;WITH a AS ( 
  SELECT CASE WHEN o.schema_id = 4 THEN CASE WHEN o.type = 'S' THEN 1 WHEN o.type = 'IT' THEN 2 ELSE 3 END ELSE o.object_id END AS object_id 
  , o.type AS otype, d.index_id, i.data_space_id , d.reserved_page_count, d.used_page_count 
  , d.in_row_data_page_count, d.lob_used_page_count, d.row_overflow_used_page_count, d.row_count 
  , r.data_compression, r.partition_number, i.type itype, i.is_unique 
  , i.fill_factor , i.is_disabled, i.is_hypothetical, i.has_filter 
  FROM sys.objects o WITH(NOLOCK) 
  INNER JOIN sys.indexes i WITH(NOLOCK) ON i.object_id = o.object_id 
  LEFT JOIN sys.partitions r WITH(NOLOCK) ON r.object_id = i.object_id AND r.index_id = i.index_id 
  LEFT JOIN sys.dm_db_partition_stats d WITH(NOLOCK) ON d.partition_id = r.partition_id 
  --AND r.object_id = d.object_id AND r.index_id = d.index_id AND r.partition_number = d.partition_number 
  WHERE o.type <> 'TF' AND o.type <> 'IT' 
  AND (@objid IS NULL OR o.object_id = @objid) 
  AND (@psid IS NULL OR i.data_space_id = @psid) 
  AND (@minrow = 0 OR row_count > @minrow) 
), b AS ( 
SELECT object_id, index_id, otype, itype, data_space_id -- MAX(CASE WHEN index_id <= 1 THEN data_space_id ELSE 0 END) data_space_id 
  , CASE WHEN COUNT(*) > 1 THEN 1 ELSE 0 END Part, COUNT(*) AS Cnt 
  , reserved = 8*SUM(reserved_page_count) , used = 8*SUM(used_page_count) 
  , in_row_data = 8*SUM(in_row_data_page_count) , lob_used = 8*SUM(lob_used_page_count) 
  , row_overflow_used = 8*SUM(row_overflow_used_page_count) , row_count = SUM(row_count) 
  , compressed = SUM(data_compression) -- change to 0 for SQL Server 2005 
  , Pop = SUM(CASE WHEN row_count = 0 OR index_id > 1 THEN 0 ELSE 1 END) 
  , Ppz = SUM(CASE WHEN row_count = 0 AND index_id <= 1 THEN 1 ELSE 0 END) 
  , Clus = MAX(CASE a.index_id WHEN 1 THEN 1 ELSE 0 END) , IxCt = MAX(CASE itype WHEN 2 THEN 1 ELSE 0 END) 
  , XmlC = MAX(CASE itype WHEN 3 THEN 1 ELSE 0 END), SpaC = MAX(CASE itype WHEN 4 THEN 1 ELSE 0 END) 
  , CoSC = MAX(CASE itype WHEN 5 THEN 1 ELSE 0 END) 
  , ncs  = MAX(CASE itype WHEN 6 THEN -1 ELSE 0 END) 
  , MO   = MAX(CASE itype WHEN 7 THEN 1 ELSE 0 END) 
  , Uniq = MAX(CASE is_unique WHEN 1 THEN 1 ELSE 0 END) 
  , disa = MAX(CASE is_disabled WHEN 1 THEN 1 ELSE 0 END) , hypo = MAX(CASE is_hypothetical WHEN 1 THEN 1 ELSE 0 END) 
  , filt = MAX(CASE has_filter WHEN 1 THEN 1 ELSE 0 END) 
  FROM a GROUP BY object_id, index_id, otype, itype , data_space_id 
) 
  INSERT @c 
  SELECT CASE WHEN otype IS NULL THEN 'A' ELSE otype END otype 
, CASE WHEN b.object_id IS NULL THEN 0 ELSE b.object_id END AS object_id 
, CASE WHEN b.object_id IS NULL THEN 0 WHEN b.object_id IN (1,2) THEN b.object_id ELSE 3 END Ord --, data_space_id 
, MAX(CASE WHEN index_id <= 1 THEN data_space_id ELSE 0 END) data_space_id 
, [Rows] = SUM(CASE WHEN b.index_id < 2 THEN b.row_count ELSE 0 END) , Reserved = SUM(b.reserved), Used = SUM(b.used) 
, Data = SUM(CASE WHEN (b.index_id < 2) THEN (b.in_row_data + b.lob_used + b.row_overflow_used)   ELSE b.lob_used + b.row_overflow_used END) 
, index2 = SUM(CASE WHEN b.index_id > 1 AND itype=2 THEN (b.in_row_data) ELSE 0 END) 
, index3 = SUM(CASE WHEN b.index_id > 1 AND itype>2 THEN (b.used) ELSE 0 END) 
, in_row_data = SUM(in_row_data), lob = SUM(lob_used), ovrflw = SUM(row_overflow_used) 
, SUM(CASE compressed WHEN 0 THEN 0 ELSE 1 END) Cmpr , SUM(CASE WHEN b.object_id > 10 AND Part > 0 THEN 1 ELSE 0 END) AS Part 
, SUM(Pop) Pop, SUM(Ppz) Ppz 
, MAX(CASE WHEN b.object_id < 10 AND disa = 0 THEN Cnt ELSE 0 END) AS Cnt 
, SUM(Clus) Clus, SUM(IxCt) IxCt, SUM(XmlC) XmlC, SUM(SpaC) SpaC 
, SUM(CoSC) CoSC, SUM(ncs) ncs, SUM(Uniq) Uniq, SUM(disa) disa, SUM(hypo) hypo, SUM(filt) filt --, SUM(MO) MO 
FROM b GROUP BY b.object_id, otype -- , data_space_id 
WITH ROLLUP HAVING (b.object_id IS NOT NULL AND otype IS NOT NULL /*AND data_space_id IS NOT NULL*/ ) OR b.object_id IS NULL 
  
DECLARE @l TABLE( object_id int primary key , CIxSk bigint , IxSk bigint , Scans bigint , lkup bigint , upd bigint , ZrIx int ) 
INSERT @l 
SELECT object_id , SUM(CASE index_id WHEN 1 THEN user_seeks ELSE 0 END) CIxSk 
 , SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_seeks END) IxSk 
 , SUM( CASE WHEN index_id < 2 THEN user_scans ELSE 0 END) Scans 
 , SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_lookups END) lkup 
 , SUM( CASE WHEN index_id < 2 THEN 0 ELSE user_updates END) upd 
 , SUM( CASE WHEN index_id > 1 AND user_seeks = 0 THEN 1 ELSE user_updates END) ZrIx 
 FROM sys.dm_db_index_usage_stats WITH(NOLOCK) WHERE database_id = DB_ID() GROUP BY object_id 
  
SELECT otype , CASE WHEN t.schema_id IS NULL THEN '' ELSE t.name END [Schema] 
, CASE c.object_id WHEN 0 THEN '_Total' WHEN 1 THEN '_sys' WHEN 2 THEN '_IT' ELSE o.name END [Table] 
, ClKey 
, /*CASE is_memory_optimized WHEN 1 THEN x2.rows_returned ELSE [Rows] END*/[Rows] 
, /*CASE is_memory_optimized WHEN 1 THEN memory_allocated_for_table_kb ELSE Reserved END*/ Reserved 
, /*CASE is_memory_optimized WHEN 1 THEN memory_used_by_table_kb ELSE [Data] END*/ [Data] 
, lob --, ovrflw 
, /*CASE is_memory_optimized WHEN 1 THEN memory_used_by_indexes_kb ELSE*/ index2 /*END*/[Index] --, newIx = index3 
, /*CASE is_memory_optimized WHEN 1 THEN memory_allocated_for_table_kb+memory_allocated_for_indexes_kb-memory_used_by_table_kb -memory_used_by_indexes_kb ELSE*/ Reserved - Used /*END*/ Unused 
, AvBR = CASE [Rows] WHEN 0 THEN 0 ELSE 1024*[Data]/ [Rows]END 
, CASE WHEN c.object_id IN (1,2,3) THEN Cnt ELSE Clus END Clus , IxCt, Uniq , XmlC Xm, SpaC Sp, CoSC + ncs cs 
, /*CASE is_memory_optimized WHEN 1 THEN 1 ELSE 0 END*/ 0 MO 
, Stct, kct, Cmpr , Part, Pop, Ppz -- , Cnt 
, CIxSk, IxSk, Scans, lkup, upd , cols, guids, ngu 
, c.data_space_id dsid , CASE y.lob_data_space_id WHEN 0 THEN NULL ELSE y.lob_data_space_id END lobds  --, fif.ftct, fif.ftsz 
, rkey, fkey, def, trg --, cols 
, disa --, hypo 
, filt , o.create_date 
FROM @c c  
LEFT JOIN @ClK j ON j.object_id = c.object_id 
LEFT JOIN sys.objects o WITH(NOLOCK) ON o.object_id = c.object_id 
LEFT JOIN sys.tables y WITH(NOLOCK) ON y.object_id = c.object_id 
LEFT JOIN sys.schemas t WITH(NOLOCK) ON t.schema_id = o.schema_id 
--LEFT JOIN sys.dm_db_xtp_table_memory_stats x ON x.object_id = y.object_id 
--LEFT JOIN sys.dm_db_xtp_index_stats x2 ON x2.object_id = y.object_id AND x2.index_id = 0 
LEFT JOIN ( 
 SELECT CASE WHEN object_id IS NULL THEN 0 ELSE object_id END object_id, COUNT(*) Stct FROM sys.stats WITH(NOLOCK) 
 WHERE object_id > 3 /* skip low values */ GROUP BY object_id 
 WITH ROLLUP HAVING object_id IS NOT NULL OR object_id IS NULL ) s ON s.object_id = c.object_id 
LEFT JOIN ( 
 SELECT table_id, SUM(data_size)/1024 ftsz , COUNT(*) ftct 
 FROM sys.fulltext_index_fragments WITH(NOLOCK) WHERE [status] = 4 GROUP BY table_id  ) fif ON fif.table_id = c.object_id 
LEFT JOIN ( 
  SELECT object_id, COUNT(*) kct FROM sys.index_columns WITH(NOLOCK) WHERE index_id = 1 GROUP BY object_id) k ON k.object_id = c.object_id 
LEFT JOIN ( 
  SELECT CASE WHEN object_id IS NULL THEN 0 ELSE object_id END object_id, COUNT(*) cols 
  , SUM(CASE system_type_id WHEN 36 THEN 1 ELSE 0 END) guids 
  , SUM(CASE WHEN system_type_id = 36 AND is_nullable = 1 THEN 1 ELSE 0 END) ngu 
 FROM sys.columns WITH(NOLOCK) GROUP BY object_id 
 /*WITH ROLLUP HAVING object_id IS NOT NULL OR object_id IS NULL*/ ) e  ON e.object_id = c.object_id 
LEFT JOIN ( SELECT CASE WHEN referenced_object_id IS NULL 
 THEN 0 ELSE referenced_object_id END referenced_object_id, COUNT(*) rkey 
 FROM sys.foreign_keys WITH(NOLOCK) GROUP BY referenced_object_id 
 /*WITH ROLLUP HAVING referenced_object_id IS NOT NULL OR referenced_object_id IS NULL*/ ) r ON r.referenced_object_id = c.object_id 
LEFT JOIN ( SELECT CASE WHEN parent_object_id IS NULL THEN 0 ELSE parent_object_id END parent_object_id, COUNT(*) fkey 
 FROM sys.foreign_keys WITH(NOLOCK) GROUP BY parent_object_id 
 /* WITH ROLLUP HAVING parent_object_id IS NOT NULL OR parent_object_id IS NULL */ 
) f ON f.parent_object_id = c.object_id 
LEFT JOIN ( SELECT CASE WHEN parent_object_id IS NULL THEN 0 ELSE parent_object_id END parent_object_id, COUNT(*) def 
 FROM sys.default_constraints WITH(NOLOCK) GROUP BY parent_object_id 
 /*WITH ROLLUP HAVING parent_object_id IS NOT NULL OR parent_object_id IS NULL*/ 
) d ON d.parent_object_id = c.object_id 
LEFT JOIN ( SELECT CASE WHEN parent_id IS NULL THEN 0 ELSE parent_id END parent_id, COUNT(*) trg 
 FROM sys.triggers WITH(NOLOCK) 
 WHERE parent_id > 0 GROUP BY parent_id 
 /*WITH ROLLUP HAVING parent_id IS NOT NULL OR parent_id IS NULL*/ 
) g ON g.parent_id = c.object_id 
LEFT JOIN @l l ON l.object_id = c.object_id 
WHERE --o.type IN ('U','V') AND 
(c.object_id IS NOT NULL /*OR x.object_id IS NOT NULL*/) 
--WHERE (--t.name <>'dbo' AND o.name NOT LIKE 'Trace%') OR t.name IS NULL 
ORDER BY Ord, Reserved DESC 
--, t.name, o.name 
OPTION ( RECOMPILE ) 
GO 
-- Then mark the procedure as a system procedure. 
EXEC sys.sp_MS_marksystemobject 'sp_spaceused2' 
GO 
SELECT NAME, IS_MS_SHIPPED FROM SYS.OBJECTS WHERE NAME LIKE 'sp_spaceused2%' 
GO