Database Reindexing (MSSQL)

There are a number of ways to reindex a database. This is my preferred method.

First, execute the below to check the fragmentation level of the active database:

declare @fragmentation float

set @fragmentation = 20

SELECT 
    S.name as [Schema],
    T.name as [Table],
    I.name as [Index],
    DDIPS.avg_fragmentation_in_percent
FROM 
    sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
INNER JOIN 
    sys.tables T with (nolock) 
on 
(
    T.object_id = DDIPS.object_id
)
INNER JOIN 
    sys.schemas S with (nolock) 
on 
(
    T.schema_id = S.schema_id
)
INNER JOIN 
    sys.indexes I with (nolock) 
ON 
(
    I.object_id = DDIPS.object_id AND 
    DDIPS.index_id = I.index_id
)
WHERE 
    DDIPS.database_id = DB_ID() and 
    I.name is not null AND 
    DDIPS.avg_fragmentation_in_percent > @fragmentation
ORDER BY 
    DDIPS.avg_fragmentation_in_percent desc

Run the database reindex by executing the following:

declare @fragmentation float

set @fragmentation = 20

declare @schema_name sysname,
        @table_name sysname,
        @index_name sysname

DECLARE cursor_tables 
    CURSOR FOR 
        SELECT 
            S.name as 'Schema',
            T.name as 'Table',
            I.name as 'Index'
        FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS DDIPS
        INNER JOIN sys.tables T with (nolock) on (T.object_id = DDIPS.object_id)
        INNER JOIN sys.schemas S with (nolock) on (T.schema_id = S.schema_id)
        INNER JOIN sys.indexes I with (nolock) ON (I.object_id = DDIPS.object_id AND DDIPS.index_id = I.index_id)
        WHERE 
            DDIPS.database_id = DB_ID() and 
            I.name is not null AND 
            DDIPS.avg_fragmentation_in_percent > @fragmentation
        ORDER BY 
            DDIPS.avg_fragmentation_in_percent desc

open cursor_tables

fetch next from cursor_tables
into @schema_name, @table_name, @index_name

while @@fetch_status = 0
begin
    exec
    (
        '
            ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + ']
            REBUILD WITH(FILLFACTOR = 80, SORT_IN_TEMPDB = ON, STATISTICS_NORECOMPUTE = ON)
        '
    )

    exec
    (
        '
            ALTER INDEX [' + @index_name + '] ON [' + @schema_name + '].[' + @table_name + ']
            REORGANIZE
        '
    )

    fetch next from cursor_tables
    into @schema_name, @table_name, @index_name
end

close cursor_tables
deallocate cursor_tables

And finally, update statistics by running this

EXEC sp_updatestats

Article created mostly for personal reference; there are of course a few different ways in which these scripts could be improved, but when providing them to clients the simplest solution is often the best.