Add missing index sql

 Add Missing Index SQL

Nghia Song -  Microsoft Dynamics 365 Technical Consultant

Nghia Song

Tel - WhatsApp: +84967324794

Email: songnghia.uit@gmail.com

USE [DBname]

GO

DECLARE @INDEXLISTCURSOR CURSOR

DECLARE @TABLELISTCURSOR CURSOR

DECLARE @TABLENAME NVARCHAR(500)

DECLARE @INDEXNAME NVARCHAR(500)

DECLARE @SQL NVARCHAR(MAX)

SELECT '['+ SCHEMA_NAME(ST.schema_id) + +'].['+ object_name(ST.object_id) +']' [Table Name],

SI.name [Index Name]

INTO #TEMPINDEXLIST

FROM sys.tables ST WITH (nolock)

JOIN sys.indexes SI WITH (nolock)

ON ST.object_id = SI.object_id

WHERE ST.is_ms_shipped = 0 and ST.type = 'U' and SI.name is not null

SELECT '['+SCHEMA_NAME(schema_id)+'].['+ ST.name +']' [Table Name]

INTO #TEMPTABLELIST

FROM sys.tables ST

--REBUILD INDEX

SET @INDEXLISTCURSOR = CURSOR FAST_FORWARD

FOR

SELECT [TABLE NAME], [INDEX NAME]

FROM #TEMPINDEXLIST

OPEN @INDEXLISTCURSOR

FETCH NEXT FROM @INDEXLISTCURSOR

INTO @TABLENAME, @INDEXNAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'ALTER INDEX [' + @INDEXNAME +

'] ON ' + @TABLENAME +

' REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)'

EXEC (@SQL)

PRINT @SQL

FETCH NEXT FROM @INDEXLISTCURSOR

INTO @TABLENAME, @INDEXNAME

END

CLOSE @INDEXLISTCURSOR

--UPDATE STATISTIC

SET @TABLELISTCURSOR = CURSOR FAST_FORWARD

FOR

SELECT [TABLE NAME]

FROM #TEMPTABLELIST

OPEN @TABLELISTCURSOR

FETCH NEXT FROM @TABLELISTCURSOR

INTO @TABLENAME

WHILE @@FETCH_STATUS = 0

BEGIN

SET @SQL = 'UPDATE STATISTICS ' + @TABLENAME + ' WITH FULLSCAN,COLUMNS'

EXEC (@SQL)

PRINT @SQL

FETCH NEXT FROM @TABLELISTCURSOR

INTO @TABLENAME

END

CLOSE @TABLELISTCURSOR

DROP TABLE #TEMPINDEXLIST, #TEMPTABLELIST

DEALLOCATE @INDEXLISTCURSOR

DEALLOCATE @TABLELISTCURSOR