Script all Indexes

  • I know this is an old post, but I would advise to be very careful with this script.

    In bol under indexproperty:

    IsPageLockDisallowed

    Page-locking value set by the ALLOW_PAGE_LOCKS option of ALTER INDEX.

    1 = Page locking is disallowed.

    0 = Page locking is allowed.

    Script states

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)

    SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '

    ELSE

    SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, '

    This is the oposite and can cause serious performance loss (extra locking).

  • Thanks for the script it worked perfectly.

  • Thank you for the script. It was very helpful!

    Rob

  • Lisa thanks I needed to have the Include columns and you saved me a lot of time! Thanks to everyone this script rocks!

  • There is another flaw in the script. The columns in the index may not be generated in the correct order.

    OLD:

    -- Get all columns of the index

    DECLARE curidxcolumn CURSOR

    FOR

    SELECT sc.column_id AS columnidintable

    ,sc.NAME

    ,ic.index_column_id columnidinindex

    ,ic.is_included_column AS isincludedcolumn

    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 = @idxTableID AND index_id = @idxid

    ORDER BY ic.index_column_id

    REPLACE WITH:

    -- Get all columns of the index

    DECLARE curidxcolumn CURSOR

    FOR

    SELECT sc.column_id AS columnidintable

    ,sc.NAME

    ,ic.index_column_id columnidinindex

    ,ic.is_included_column AS isincludedcolumn

    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 = @idxTableID AND index_id = @idxid

    ORDER BY ic.key_ordinal

    Full Script:

    DECLARE @idxTableName SYSNAME

    DECLARE @idxTableID INT

    DECLARE @idxname SYSNAME

    DECLARE @idxid INT

    DECLARE @colCount INT

    DECLARE @IxColumn SYSNAME

    DECLARE @IxFirstColumn BIT

    DECLARE @ColumnIDInTable INT

    DECLARE @ColumnIDInIndex INT

    DECLARE @IsIncludedColumn INT

    DECLARE @sIncludeCols VARCHAR(4000)

    DECLARE @sIndexCols VARCHAR(4000)

    DECLARE @sSQL VARCHAR(4000)

    DECLARE @rowcnt INT

    DECLARE @sParamSQL VARCHAR(4000)

    DECLARE @location SYSNAME

    DECLARE @fillfactor INT

    -- Get all the index info

    DECLARE curidx CURSOR

    FOR

    SELECT object_name(si.object_id)

    ,si.object_id

    ,si.NAME

    ,si.index_id

    FROM sys.indexes si

    LEFT JOIN information_schema.table_constraints tc ON si.NAME = tc.constraint_name AND object_name(si.object_id) = tc.table_name

    WHERE objectproperty(si.object_id, 'IsUserTable') = 1

    ORDER BY object_name(si.object_id)

    ,si.index_id

    OPEN curidx

    FETCH NEXT

    FROM curidx

    INTO @idxTableName

    ,@idxTableID

    ,@idxname

    ,@idxid

    --loop

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @sSQL = 'IF NOT EXISTS (SELECT 1 FROM SYS.INDEXES WHERE name = ''' + @idxname + ''')' + CHAR(13)

    SET @sSQL = @sSQL + 'BEGIN' + CHAR(13)

    SET @sSQL = @sSQL + 'CREATE '

    -- Check if the index is unique

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)

    SET @sSQL = @sSQL + 'UNIQUE '

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)

    SET @sSQL = @sSQL + 'CLUSTERED '

    SET @sSQL = @sSQL + 'INDEX ' + @idxname + ' ON ' + @idxTableName + CHAR(13) + '('

    SET @sSQL = @sSQL + CHAR(13)

    SET @colCount = 0

    SELECT @fillfactor = fill_factor

    FROM sys.indexes

    WHERE name = @idxname

    IF ISNULL(@fillfactor, 0) = 0

    SET @fillfactor = 90

    -- Get the number of cols in the index

    SELECT @colCount = COUNT(*)

    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 = @idxtableid AND index_id = @idxid AND ic.is_included_column = 0

    -- Get the file group info

    SELECT @location = f.[name]

    FROM sys.indexes i

    INNER JOIN sys.filegroups f ON i.data_space_id = f.data_space_id

    INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id]

    WHERE o.object_id = @idxTableID AND i.index_id = @idxid

    -- Get all columns of the index

    DECLARE curidxcolumn CURSOR

    FOR

    SELECT sc.column_id AS columnidintable

    ,sc.NAME

    ,ic.index_column_id columnidinindex

    ,ic.is_included_column AS isincludedcolumn

    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 = @idxTableID AND index_id = @idxid

    ORDER BY ic.key_ordinal

    SET @IxFirstColumn = 1

    SET @sIncludeCols = ''

    SET @sIndexCols = ''

    SET @rowcnt = 0

    OPEN curidxColumn

    FETCH NEXT

    FROM curidxColumn

    INTO @ColumnIDInTable

    ,@IxColumn

    ,@ColumnIDInIndex

    ,@IsIncludedColumn

    --loop

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    IF @IsIncludedColumn = 0

    BEGIN

    SET @rowcnt = @rowcnt + 1

    SET @sIndexCols = CHAR(9) + @sIndexCols + '[' + @IxColumn + ']'

    -- Check the sort order of the index cols

    IF (INDEXKEY_PROPERTY(@idxTableID, @idxid, @ColumnIDInIndex, 'IsDescending')) = 0

    SET @sIndexCols = @sIndexCols + ' ASC '

    ELSE

    SET @sIndexCols = @sIndexCols + ' DESC '

    IF @rowcnt < @colCount

    SET @sIndexCols = @sIndexCols + ', '

    END

    ELSE

    BEGIN

    -- Check for any include columns

    IF len(@sIncludeCols) > 0

    SET @sIncludeCols = @sIncludeCols + ','

    SET @sIncludeCols = @sIncludeCols + '[' + @IxColumn + ']'

    END

    FETCH NEXT

    FROM curidxColumn

    INTO @ColumnIDInTable

    ,@IxColumn

    ,@ColumnIDInIndex

    ,@IsIncludedColumn

    END

    CLOSE curidxColumn

    DEALLOCATE curidxColumn

    --append to the result

    IF LEN(@sIncludeCols) > 0

    SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '

    ELSE

    SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '

    -- Build the options

    SET @sParamSQL = ' WITH (FILLFACTOR = ' + cast(isnull(@fillfactor, 90) AS VARCHAR(3)) + ', '

    --set @sParamSQL = ' WITH ('

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1)

    SET @sParamSQL = @sParamSQL + ' PAD_INDEX = ON, '

    ELSE

    SET @sParamSQL = @sParamSQL + ' PAD_INDEX = OFF, '

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 1)

    SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = OFF, '

    ELSE

    SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = ON, '

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 1)

    SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = OFF, '

    ELSE

    SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = ON, '

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)

    SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = ON, '

    ELSE

    SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = OFF, '

    SET @sParamSQL = @sParamSQL + ' DROP_EXISTING = ON ) '

    SET @sIndexCols = @sIndexCols + CHAR(13) + @sParamSQL + ' ON [' + @location + ']' + CHAR(13) + 'END ' + CHAR(10) + 'GO' + CHAR(13)

    PRINT @sIndexCols

    FETCH NEXT

    FROM curidx

    INTO @idxTableName

    ,@idxTableID

    ,@idxname

    ,@idxid

    END

    CLOSE curidx

    DEALLOCATE curidx

  • Just ran across this script - it works quite nicely for me.

    "I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

    -- Itzik Ben-Gan 2001

Viewing 6 posts - 31 through 35 (of 35 total)

You must be logged in to reply to this topic. Login to reply