sp_spaceused2

Joe Chang, 2018-10-24 (first published: 2018-10-15)

this is created as a system stored procedure in master, 

you will need permission to mark as system object.

There may be others who have also created their own procedure with this name, so adjust as appropriate

EXEC sys.sp_MS_marksystemobject '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

Rate

3.5 (2)

Share

Share

Rate

3.5 (2)