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

-- updates 2018-03-06
-- 2018-04-08 sys.stats is_incremental
USE master -- skip this for Azure
GO

IF EXISTS (
 SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('sp_helpindex2')
) DROP procedure [dbo].sp_helpindex2
GO

CREATE PROCEDURE [dbo].[sp_helpindex2]
 @objname nvarchar(776)
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)
if @objid is NULL
begin
 raiserror(15009, -1,-1, @objname,@dbname)
 return (1)
end

;WITH b AS (
 SELECT d.object_id, d.index_id, part = COUNT(*) , pop = SUM(CASE row_count WHEN 0 THEN 0 ELSE 1 END)
 , reserved = 8*SUM(d.reserved_page_count)
 , used = 8*SUM(d.used_page_count)
 , in_row_data = 8*SUM(d.in_row_data_page_count)
 , lob_used = 8*SUM(d.lob_used_page_count)
 , overflow = 8*SUM(d.row_overflow_used_page_count)
 , row_count = SUM(row_count)
 , notcompressed = SUM(CASE data_compression WHEN 0 THEN 1 ELSE 0 END)
 , compressed = SUM(CASE data_compression WHEN 0 THEN 0 ELSE 1 END) -- change to 0 for SQL Server 2005

 FROM sys.dm_db_partition_stats d WITH(NOLOCK)
 INNER JOIN sys.partitions r WITH(NOLOCK) ON r.partition_id = d.partition_id
 GROUP BY d.object_id, d.index_id

), j AS (
 SELECT j.object_id, j.index_id, j.key_ordinal, c.column_id, c.name, j.is_descending_key, j.is_included_column
 , j.partition_ordinal
 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 ISNULL(i.name, '')[index]
, ISNULL(STUFF(( SELECT ', ' + name + CASE is_descending_key WHEN 1 THEN '-' ELSE '' END
  + CASE partition_ordinal WHEN 1 THEN '*' ELSE '' END
   FROM j WHERE j.object_id = i.object_id AND j.index_id= i.index_id AND j.key_ordinal > 0
   ORDER BY j.key_ordinal FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'),1,1, '') ,'') as Keys

, ISNULL(STUFF(( SELECT ', ' + name + CASE partition_ordinal WHEN 1 THEN '*' ELSE '' END
   FROM j WHERE j.object_id = i.object_id AND j.index_id = i.index_id
   AND (j.is_included_column= 1 OR (j.key_ordinal= 0 AND partition_ordinal = 1) )
   ORDER BY j.column_id FOR XML PATH(''), TYPE, ROOT).value('root[1]', 'nvarchar(max)'),1,1, '') ,'') as Incl
--, j.name AS ptky
, i.index_id
, CASE WHEN i.is_primary_key = 1 THEN 'PK'
   WHEN i.is_unique_constraint= 1 THEN 'UC'
   WHEN i.is_unique = 1 THEN 'U'
   WHEN i.type = 0 THEN 'heap'
   WHEN i.type = 3 THEN 'X'
   WHEN i.type = 4 THEN 'S'
   ELSE CONVERT(char, i.type) END typ
, i.data_space_id dsi
, b.row_count
, b.in_row_data in_row , b.overflow ovf , b.lob_used lob
, b.reserved - b.in_row_data - b.overflow- b.lob_used unu
, 'ABR' = CASE row_count WHEN 0 THEN 0 ELSE 1024*used/row_count END
, y.user_seeks, y.user_scans u_scan, y.user_lookups u_look, y.user_updates u_upd
, b.notcompressed ncm , b.compressed cmp , b.pop, b.part
, rw_delta = b.row_count - s.rows, s.rows_sampled --, s.unfiltered_rows
, s.modification_counter mod_ctr, s.steps
, CONVERT(varchar, s.last_updated,120) updated
, i.is_disabled dis, i.is_hypothetical hyp, ISNULL(i.filter_definition, '') filt
, t.no_recompute no_rcp , t.is_incremental incr

FROM sys.objects o
JOIN sys.indexes i ON i.object_id = o.object_id

LEFT JOIN sys.stats t ON t.object_id = o.object_id AND t.stats_id = i.index_id
LEFT JOIN b ON b.object_id = i.object_id AND b.index_id = i.index_id
LEFT JOIN sys.dm_db_index_usage_stats y ON y.object_id = i.object_id AND y.index_id = i.index_id
AND y.database_id = DB_ID()
OUTER APPLY sys.dm_db_stats_properties(i.object_id, i.index_id) s
--LEFT JOIN j ON j.object_id = i.object_id AND j.index_id = i.index_id AND j.partition_ordinal = 1

WHERE i.object_id = @objid
GO

-- Then mark the procedure as a system procedure.
EXEC sys.sp_MS_marksystemobject 'sp_helpindex2' -- skip this for Azure
GO
SELECT NAME, IS_MS_SHIPPED FROM SYS.OBJECTS WHERE NAME LIKE 'sp_helpindex%'
GO

--DROP PROCEDURE dbo.sp_helpindex2