Add missing index sql - Song Nghia - Technical Consultant

Dynamic 365 FO Dynamic 365 Retail

Thứ Năm, 3 tháng 3, 2022

Add missing index sql

 Add Missing Index SQL

Song Nghia - Technical Consultant




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