Work with Indexes on SQL Server

SELECT
obj.name,
ps.object_id,
ps.index_id,
ps.partition_number AS partitionnum,
ps.avg_fragmentation_in_percent AS frag,
si.allow_page_locks
,index_type_desc
,ps.index_id
FROM 
sys.objects obj
JOIN sys.dm_db_index_physical_stats (db_id(), NULL, NULL , NULL, 'LIMITED') ps ON obj.object_id = ps.object_id
join (select object_id,index_id,allow_page_locks from sys.indexes where is_disabled=0 ) as si
on si.object_id=ps.object_id
and si.index_id=ps.index_id
WHERE 1=1
--and ps.avg_fragmentation_in_percent > 10.0
--AND ps.index_id > 0
--AND index_type_desc='NONCLUSTERED INDEX'
and obj.name IN ('table')

-- REBUILD INDEXEX
DECLARE @indexes table (TABLE_NAME nvarchar(100), INDEX_NAME nvarchar(100))

INSERT INTO @indexes
select
t.name as TABLE_NAME
,i.name as INDEX_NAME
from sys.tables t
join sys.indexes i
on i.object_id = t.object_id
where t.name in 
('table1','table2')

DECLARE
@SQL nvarchar(max)
,@table nvarchar(100)
,@index nvarchar(100)

WHILE ((select count(*) from @indexes) > 0)
BEGIN
SELECT TOP 1 @table = TABLE_NAME, @index = INDEX_NAME from @indexes

SELECT @SQL = 'ALTER INDEX [' + @index + '] ON [dbo].[' + @table + '] REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION = ROW)'

--PRINT(@SQL)
EXEC(@SQL)
DELETE FROM @indexes WHERE TABLE_NAME = @table and INDEX_NAME = @index
END
GO

-- UPDATE STATISTICS
UPDATE STATISTICS [dbo].[table1] WITH FULLSCAN
GO

UPDATE STATISTICS [dbo].[table2] WITH FULLSCAN
GO

SELECT
  avg_total_user_cost * avg_user_impact * (user_seeks + user_scans), s.avg_total_user_cost,s.avg_user_impact,
  s = OBJECT_SCHEMA_NAME(d.[object_id]),
  o = OBJECT_NAME(d.[object_id]),
  d.equality_columns,
  d.inequality_columns,
  d.included_columns,
  s.unique_compiles,
  s.user_seeks, s.last_user_seek,
  s.user_scans, s.last_user_scan
FROM sys.dm_db_missing_index_details AS d
INNER JOIN sys.dm_db_missing_index_groups AS g
ON d.index_handle = g.index_handle
INNER JOIN sys.dm_db_missing_index_group_stats AS s
ON g.index_group_handle = s.group_handle
WHERE d.database_id = DB_ID()
AND OBJECTPROPERTY(d.[object_id], 'IsMsShipped') = 0

--order by s.avg_user_impact desc--,last_user_seek desc
ORDER BY last_user_seek desc,
avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)DESC