March 4, 2013 at 3:58 am
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).
May 2, 2013 at 4:33 am
Thanks for the script it worked perfectly.
June 22, 2013 at 4:29 pm
Thank you for the script. It was very helpful!
Rob
April 30, 2014 at 1:06 pm
Lisa thanks I needed to have the Include columns and you saved me a lot of time! Thanks to everyone this script rocks!
June 19, 2014 at 10:55 am
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
September 17, 2015 at 9:46 pm
Just ran across this script - it works quite nicely for me.
-- 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy