Rebuild or Reorganize and statistics update of all indexes of a table.

Customer comes and say they want to rebuild\reorganize  all indexes of  a table.

1. We will get the table name from customer
2.we will use  sys.dm_db_index_physical_stats

-- Get the avg_fragmentation_in_percent report for a table & its corresponding index.

USE master;
GO
-- In this example, OBJECT_ID is evaluated in the context of the master database. 
-- Because Person.Address does not exist in master, the function returns NULL.
-- When NULL is specified as an object_id, all objects in the database are returned.
-- The same results are returned when an object that is not valid is specified.

SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'AdventureWorks'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO
-- This example demonstrates the results of specifying a valid object name
-- that exists in both the current database context and
-- in the database specified in the database_id parameter of the 
-- sys.dm_db_index_physical_stats function.
-- An error is returned because the ID value returned by OBJECT_ID does not
-- match the ID value of the object in the specified database.

CREATE DATABASE Test;
GO
USE Test;
GO
CREATE SCHEMA Person;
GO
CREATE Table Person.Address(c1 int);
GO
USE AdventureWorks2012;
GO
SELECT * FROM sys.dm_db_index_physical_stats
    (DB_ID(N'Test'), OBJECT_ID(N'Person.Address'), NULL, NULL , 'DETAILED');
GO


Generate all index name from above query and tried to convince that index reorganization will be good.

Then

USE [<DBNAME>]
GO
ALTER INDEX [<Index Name>] ON [dbo].[<Table Name>] REORGANIZE


Then update statistics of that table using below command

 SELECT

OBJECT_NAME([sp].[object_id]) AS "Table",

[sp].[stats_id] AS "Statistic ID",

[s].[name] AS "Statistic",

[sp].[last_updated] AS "Last Updated",

[sp].[rows],

[sp].[rows_sampled],

[sp].[unfiltered_rows],

[sp].[modification_counter] AS "Modifications",

'UPDATE Statistics ' + OBJECT_NAME([sp].[object_id]) + ' ( ' + [s].[name] + ')' + ' with SAMPLE 30 percent'

FROM [sys].[stats] AS [s]

OUTER APPLY sys.dm_db_stats_properties ([s].[object_id],[s].[stats_id]) AS [sp]

WHERE [s].[object_id] = OBJECT_ID(N'<TableName>');