RSS

Generate Script To drop and Recreate all index in Database

04 Jun

Hi Guys

by this Script you can Drop all indexes on your Database then Created it Again

this Cursor Script after the Execution the Result is Script With  Drop the Create all index in your database but n0t include the index on the Primary Key

DECLARE
@IncludeFileGroup bit = 1,
@IncludeDrop bit = 1,
@IncludeFillFactor bit = 1

— Get all existing indexes, but NOT the primary keys
DECLARE Indexes_cursor CURSOR
FOR SELECT
SC.Name AS SchemaName
, SO.Name AS TableName
, SI.Object_Id AS TableId
, SI.[Name] AS IndexName
, SI.Index_ID AS IndexId
, FG.[Name] AS FileGroupName
, CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END Fill_Factor
FROM sys.indexes SI
LEFT JOIN sys.filegroups FG
ON SI.data_space_id = FG.data_space_id
INNER JOIN sys.objects SO
ON SI.object_id = SO.object_id
INNER JOIN sys.schemas SC
ON SC.schema_id = SO.schema_id
WHERE ObjectProperty(SI.Object_Id, ‘IsUserTable’) = 1
AND SI.[Name] IS NOT NULL
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0
AND IndexProperty(SI.Object_Id, SI.[Name], ‘IsStatistics’) = 0
ORDER BY Object_name(SI.Object_Id), SI.Index_ID

DECLARE @SchemaName sysname
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId int
DECLARE @FillFactor int

DECLARE @NewLine nvarchar(4000) SET @NewLine = CHAR(13) + CHAR(10)
DECLARE @Tab nvarchar(4000) SET @Tab = Space(4)

— Loop through all indexes
OPEN Indexes_cursor

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName,
@IndexId, @FileGroupName, @FillFactor

WHILE (@@Fetch_Status = 0)
BEGIN

DECLARE @sIndexDesc nvarchar(4000)
DECLARE @sCreateSql nvarchar(4000)
DECLARE @sDropSql nvarchar(4000)

SET @sIndexDesc = ‘– Index ‘ + @IndexName + ‘ on table ‘ + @TableName
SET @sDropSql = ‘IF EXISTS (SELECT 1’ + @NewLine
+ ‘ FROM sysindexes si’ + @NewLine
+ ‘ INNER JOIN sysobjects so’ + @NewLine
+ ‘ ON so.id = si.id’ + @NewLine
+ ‘ WHERE si.[Name] = N”’ + @IndexName + ”’ — Index Name’ + @NewLine
+ ‘ AND so.[Name] = N”’ + @TableName + ”’) — Table Name’ + @NewLine
+ ‘BEGIN’ + @NewLine
+ ‘ DROP INDEX [‘ + @IndexName + ‘] ON
[‘ + @SchemaName + ‘].[‘ + @TableName + ‘]’ + @NewLine
+ ‘END’ + @NewLine

SET @sCreateSql = ‘CREATE’

— Check if the index is unique
IF (IndexProperty(@TableId, @IndexName, ‘ IsUnique’) = 1)
BEGIN
SET @sCreateSql = @sCreateSql + ‘ UNIQUE’
END
–END IF
— Check if the index is clustered
IF (IndexProperty(@TableId, @IndexName, ‘ IsClustered’) = 1)
BEGIN
SET @sCreateSql = @sCreateSql + ‘ CLUSTERED’
END
–END IF

SET @sCreateSql = @sCreateSql + ‘ INDEX [‘ + @IndexName + ‘]
ON [‘ + @SchemaName + ‘].[‘ + @TableName + ‘]’ + @NewLine + ‘(‘ + @NewLine

— Get all columns of the index
DECLARE IndexColumns_cursor CURSOR
FOR SELECT SC.[Name],
IC.[is_included_column],
IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC
ON IC.Object_Id = SC.Object_Id
AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_Id = @TableId
AND Index_ID = @IndexId
ORDER BY IC.key_ordinal

DECLARE @IxColumn sysname
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit SET @IxIsIncl = 0
DECLARE @IxFirstColumn bit SET @IxFirstColumn = 1

— Loop through all columns of the index and append them to the CREATE statement
OPEN IndexColumns_cursor
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc

WHILE (@@Fetch_Status = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
END
ELSE
BEGIN
–check to see if it’s an included column
IF (@IxIsIncl = 0) AND (@IxIncl = 1)
BEGIN
SET @IxIsIncl = 1
SET @sCreateSql = @sCreateSql + @NewLine + ‘)’ +
@NewLine + ‘INCLUDE’ + @NewLine + ‘(‘ + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ‘,’ + @NewLine
END
–END IF
END
–END IF

SET @sCreateSql = @sCreateSql + @Tab + ‘[‘ + @IxColumn + ‘]’
— check if ASC or DESC
IF @IxIsIncl = 0
BEGIN
IF @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ‘ DESC’
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ‘ ASC’
END
–END IF
END
–END IF
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END
–END WHILE
CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor

SET @sCreateSql = @sCreateSql + @NewLine + ‘) ‘

IF @IncludeFillFactor = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine +
‘WITH (FillFactor = ‘ + Cast(@FillFactor as varchar(13)) + ‘)’ + @NewLine
END
–END IF

IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + ‘ON [‘+ @FileGroupName + ‘]’ + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine
END
–END IF

PRINT ‘– **************************************************************’
PRINT @sIndexDesc
PRINT ‘– **************************************************************’

IF @IncludeDrop = 1
BEGIN
PRINT @sDropSql
PRINT ‘GO’
END
–END IF

PRINT @sCreateSql
PRINT ‘GO’ + @NewLine + @NewLine

FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName,
@IndexId, @FileGroupName, @FillFactor
END
–END WHILE
CLOSE Indexes_cursor
DEALLOCATE Indexes_cursor

 

 
4 Comments

Posted by on June 4, 2013 in Index, Script

 

4 responses to “Generate Script To drop and Recreate all index in Database

  1. irfanview

    March 25, 2014 at 3:57 PM

    I commeոt when I espeϲially enjoy a post on
    a site or if I haѵe something to valuable to contribut
    to the discussion. Usually it is ɑ result off the
    ƿassion displayed in tthe article I read. And after thiѕ article Generate Scгipt To
    droρ annd Recreate all index in Database DATABASE ADMINISTRATION.

    I was actuallly moved enough to drop a thouǥht :
    ) I dօ have ѕomе questions for you if it’s аllright.

    Coould it be oոly me or ddo somne of these comment appear like thеy are written
    by brain dead visitors? 😛 And, iff you are posting att
    other onlinе sites, I would like to follοw you. Could you liѕt the completе urls of your publiuc sites like your twitter
    feed, Facebooƙ paցе oor linkedin profile?

     
  2. peter

    October 15, 2015 at 12:07 AM

    You need to empty the @included_columns in your loop, as for indexes without it will try to create from the last one.

     
    • Mustafa EL-Masry

      October 15, 2015 at 12:29 AM

      Thanks Peter for your valuable Comment out can you Explain more to me your opinion

       

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s