List Resumable Index SQL SERVER

 --List Current Resumable Index

ALTER INDEX IX_ProdDate_v6 ON T_TDHEADER REBUILD 

WITH (ONLINE = ON, RESUMABLE = ON, MAXDOP=8);

ALTER INDEX IX_ProdDate_v6 ON T_TDHEADER REBUILD 

WITH (ONLINE = ON, RESUMABLE = ON);

SELECT 

name as index_name,

percent_complete, 

state_desc, 

start_time,

last_pause_time, 

total_execution_time AS ExecutionMin,

-- execution time times 

-- ratio of percent to complete and precent completed

total_execution_time * (100.0-percent_complete)/percent_complete  

    AS ApproxExecutionMinLeft 

FROM sys.index_resumable_operations;

ALTER INDEX IX_ProdDate_v6 ON T_TDHEADER PAUSE;

ALTER INDEX IX_ProdDate_v6 ON T_TDHEADER RESUME;

ALTER INDEX IX_ProdDate_v6 ON T_TDHEADER ABORT;

List Partition SQL SERVER

-- List Partition by DAV

  SELECT distinct f.NAME AS file_group_name,

SCHEMA_NAME(t.schema_id) AS table_schema,

t.name AS table_name,

p.partition_number,

ISNULL(CAST(left_prv.value AS VARCHAR(MAX))+ CASE WHEN pf.boundary_value_on_right = 0 THEN ' < '

ELSE ' <= '

END , '-INF < ')

+ 'X' + ISNULL(CASE WHEN pf.boundary_value_on_right = 0 THEN ' <= '

ELSE ' < '

END + CAST(right_prv.value AS NVARCHAR(MAX)), ' < INF') AS range_desc,

p.rows AS NumberOfRows,

pf.boundary_value_on_right,

ps.name AS partition_schem_name,

pf.name AS partition_function_name,

left_prv.value AS left_boundary,

right_prv.value AS right_boundary 

FROM sys.partitions p

JOIN sys.tables t

ON p.object_id = t.object_id

JOIN sys.indexes i

ON p.object_id = i.object_id

AND p.index_id = i.index_id

JOIN sys.allocation_units au

ON p.hobt_id = au.container_id

JOIN sys.filegroups f

ON au.data_space_id = f.data_space_id

LEFT JOIN sys.partition_schemes ps

ON ps.data_space_id = i.data_space_id

LEFT JOIN sys.partition_functions pf

ON ps.function_id = pf.function_id

LEFT JOIN sys.partition_range_values left_prv

ON left_prv.function_id = ps.function_id

AND left_prv.boundary_id + 1 = p.partition_number

LEFT JOIN sys.partition_range_values right_prv

ON right_prv.function_id = ps.function_id

AND right_prv.boundary_id = p.partition_number

where t.name in ('Partitioned_TShippment','STAGE_TShippment');

List Tables on SQL Server

 SELECT

  name,

  crdate

FROM

  SYSOBJECTS

WHERE

  xtype = 'U' and name like '%Shi%';

GO

select * from CommandLog where command like 'ALTER INDEX%';