-- SQL Scripts , sp_spaceused , sp_helpindex2 , sp_partitions , sp_updatestats2

-- update 2018-02-24
USE master
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
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)

;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

), c AS (
  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

) , j 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

)

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]

, ISNULL(STUFF(( SELECT ', ' + name + CASE is_descending_key WHEN 1 THEN '-' ELSE '' END

   FROM j WHERE j.object_id = c.object_id AND j.index_id = 1 AND j.key_ordinal >0

   ORDER BY j.key_ordinal FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'), 1,1,'') ,'') as 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 

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 ( 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

) l ON l.object_id = c.object_id

/*LEFT JOIN ( SELECT object_id , SUM(CASE WHEN s.avg_user_impact > 90.0 THEN 1 ELSE 0 END) mix

FROM sys.dm_db_missing_index_details d WITH(NOLOCK)

INNER JOIN sys.dm_db_missing_index_groups g WITH(NOLOCK) ON g.index_handle = d.index_handle

INNER JOIN sys.dm_db_missing_index_group_stats s WITH(NOLOCK) ON s.group_handle = g.index_group_handle

WHERE d.database_id = DB_ID() GROUP BY object_id

) m ON m.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

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