-- SQL Scripts , sp_spaceused2 , sp_helpindex2 , sp_partitions , sp_updatestats2 , sp_vas , sp_plancache_flush

-- update 2018-10-11: now using table variables in place of CTEs
-- update 2018-10-24: query to sys.dm_db_partition_stats uses HASH join hint
-- separate SQL to handle single table versus all or filegroup
-- allow objname parameter to be a comma delimited list of table? sp_spaceused2 (STRING_SPLIT)

USE master -- skip this for 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 bigint = 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)


-- Main queries

/*
-- table/view list -- for use with SQL Server 2016 and later, or substitute STRING_SPLIT function
DECLARE @objs int
DECLARE @obj TABLE( object_id int primary key)
INSERT @obj
SELECT OBJECT_ID(value) FROM STRING_SPLIT(@objname, ',')
SELECT @objs = @@ROWCOUNT
*/

 

-- Clustered Index Key
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, 2,'') ,'') as ClKey
FROM sys.indexes c WHERE c. index_id = 1
;

-- space used
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) )
;

-- IF @objs > 0
IF @objid IS NOT NULL
WITH a AS ( -- first CTE
  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 d.object_id IN (SELECT object_id FROM @obj) -- this seems to help
  --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 o.object_id IN (SELECT object_id FROM @obj)
  AND (@psid IS NULL OR i.data_space_id = @psid)
  AND (@minrow = 0 OR row_count > @minrow)

)
, b AS ( -- second CTE
  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

 

ELSE -- all objects or file group
WITH a AS ( -- first CTE
  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 HASH JOIN sys.dm_db_partition_stats d WITH(NOLOCK) ON d.partition_id = r.partition_id -- note use of join hint
  --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 ( -- second CTE
  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

 

-- index usage stats
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

 

-- final query
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