Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234

Script all Indexes Expand / Collapse
Author
Message
Posted Monday, March 4, 2013 3:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Today @ 7:43 AM
Points: 8, Visits: 390
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).



Post #1426165
Posted Thursday, May 2, 2013 4:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, December 3, 2013 3:51 AM
Points: 1, Visits: 72
Thanks for the script it worked perfectly.
Post #1448755
Posted Saturday, June 22, 2013 4:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, July 7, 2014 3:12 PM
Points: 4, Visits: 85
Thank you for the script. It was very helpful!

Rob
Post #1466491
Posted Wednesday, April 30, 2014 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, July 25, 2014 8:06 AM
Points: 1, Visits: 233
Lisa thanks I needed to have the Include columns and you saved me a lot of time! Thanks to everyone this script rocks!
Post #1566568
Posted Thursday, June 19, 2014 10:55 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 19, 2014 11:36 AM
Points: 1, Visits: 26
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

Post #1583971
« Prev Topic | Next Topic »

Add to briefcase «««1234

Permissions Expand / Collapse