SQL Server 2005: Script all Indexes

  • Comments posted to this topic are about the item SQL Server 2005: Script all Indexes

  • You forgot to declare @PKSQL

  • The variable @PKSQL is not used, so just comment it out. Then the script works OK.

    DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''

    to

    DECLARE @IXSQL NVARCHAR(4000) --SET @PKSQL = ''

    Then it works fine. It saved me quite a lot of time

    thanks

    http://90.212.51.111 domain

  • The "include" index param is missing. I've refactored the script as follows:

    -- Get all existing indexes, but NOT the primary keys

    DECLARE cIX CURSOR FOR

    SELECT OBJECT_NAME(SI.Object_ID),

    SI.Object_ID,

    SI.Name,

    SI.Index_ID,

    (select TABLE_SCHEMA from INFORMATION_SCHEMA.TABLES where TABLE_NAME = OBJECT_NAME(SI.Object_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 TC.CONSTRAINT_NAME IS NULL

    AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1

    AND OBJECT_NAME(SI.Object_ID) = @tablename

    ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

    DECLARE @IxTable sysname

    DECLARE @IxTableID INT

    DECLARE @IxName sysname

    DECLARE @i_schema sysname

    DECLARE @IxID INT

    -- Loop through all indexes

    OPEN cIX

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @i_schema

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    DECLARE @IXSQL NVARCHAR(4000) //SET @PKSQL = ''

    SET @IXSQL = 'CREATE '

    -- Check if the index is unique

    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)

    SET @IXSQL = @IXSQL + 'UNIQUE '

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)

    SET @IXSQL = @IXSQL + 'CLUSTERED '

    SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @i_schema +'.'+ @IxTable + ']('

    -- Get all columns of the index

    DECLARE cIxColumn CURSOR FOR

    SELECT SC.Name, IC.is_included_column, IC.is_descending_key

    FROM sys.index_columns IC JOIN sys.columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID

    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID

    ORDER BY IC.Index_Column_ID

    DECLARE @IxColumn sysname

    DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

    declare @i_include bit

    declare @i_desc bit

    declare @i_sql_col varchar(4000) set @i_sql_col = ''

    declare @i_sql_inc varchar(4000) set @i_sql_inc = ''

    -- Loop throug all columns of the index and append them to the CREATE statement

    OPEN cIxColumn

    FETCH NEXT FROM cIxColumn INTO @IxColumn,@i_include,@i_desc

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    if (@i_include = 1)

    set @i_sql_inc = @i_sql_inc +', ' + '[' + @IxColumn + ']'

    else

    begin

    set @i_sql_col = @i_sql_col + ', ' + '[' + @IxColumn + ']'

    if (@i_desc = 1)

    set @i_sql_col = +@i_sql_col + ' DESC'

    end

    FETCH NEXT FROM cIxColumn INTO @IxColumn,@i_include,@i_desc

    END

    CLOSE cIxColumn

    DEALLOCATE cIxColumn

    -- remove leading ','

    if (left(@i_sql_inc,1) = ',')

    set @i_sql_inc = right(@i_sql_inc,len(@i_sql_inc)-1)

    if (left(@i_sql_col,1) = ',')

    set @i_sql_col = right(@i_sql_col,len(@i_sql_col)-1)

    SET @IXSQL = @IXSQL + @i_sql_col + ')'

    if (@i_sql_inc <> '')

    set @IXSQL = @IXSQL + ' INCLUDE (' + @i_sql_inc + ')'

    print @IXSQL

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @i_schema

    END

  • Didn't declare @tablename.

    "Beliefs" get in the way of learning.

  • It didn't cater asc or desc order

  • you will want to change the order in which columns are added to the script to:

    DECLARE cIxColumn CURSOR FOR

    SELECT SC.Name

    FROM Sys.Index_Columns IC

    JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID

    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID

    ORDER BY IC.key_ordinal

    key_ordinal is the order in which the columns are built in the index; Index_Column_ID is the column ID, which does not correlate to the structure of the index.

  • Modified for INCLUDEd columns and for DESC order, and for long/irregular table and column names.

    -- Get all existing indexes, but NOT the primary keys

    DECLARE cIX 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 TC.CONSTRAINT_NAME IS NULL

    AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1

    ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

    DECLARE @IxTable SYSNAME

    DECLARE @IxTableID INT

    DECLARE @IxName SYSNAME

    DECLARE @IxID INT

    -- Loop through all indexes

    OPEN cIX

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    DECLARE @IXSQL NVARCHAR(4000)

    --SET @PKSQL = ''

    SET @IXSQL = 'CREATE '

    -- Check if the index is unique

    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)

    SET @IXSQL = @IXSQL + 'UNIQUE '

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)

    SET @IXSQL = @IXSQL + 'CLUSTERED '

    SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON [' + @IxTable + '] ('

    -- Get all columns of the index

    DECLARE cIxColumn CURSOR FOR

    SELECT SC.Name,IC.[is_included_column],IC.is_descending_key

    FROM Sys.Index_Columns IC

    JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID

    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID

    ORDER BY IC.Index_Column_ID,IC.is_included_column

    DECLARE @IxColumn SYSNAME

    DECLARE @IxIncl bit

    DECLARE @Desc bit

    DECLARE @IxIsIncl bit set @IxIsIncl = 0

    DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

    -- Loop throug all columns of the index and append them to the CREATE statement

    OPEN cIxColumn

    FETCH NEXT FROM cIxColumn 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 @IXSQL = @IXSQL + ') INCLUDE ('

    END

    ELSE

    BEGIN

    SET @IXSQL = @IXSQL + ', '

    END

    END

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

    --check to see if it's DESC

    IF @Desc = 1

    SET @IXSQL = @IXSQL + ' DESC'

    FETCH NEXT FROM cIxColumn INTO @IxColumn, @IxIncl, @Desc

    END

    CLOSE cIxColumn

    DEALLOCATE cIxColumn

    SET @IXSQL = @IXSQL + ')'

    -- Print out the CREATE statement for the index

    PRINT @IXSQL

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID

    END

    CLOSE cIX

    DEALLOCATE cIX

  • Missing the filegroup clause so adding it, adding GO clause and modifying the cursor query:

    -- Get all existing indexes, but NOT the primary keys

    DECLARE cIX CURSOR FOR

    SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID,

    FG.name

    FROM sys.indexes SI

    left outer join sys.filegroups FG

    on SI.data_space_id = FG.data_space_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 @IxTable sysname

    DECLARE @IxTableID INT

    DECLARE @IxName sysname

    DECLARE @FGName sysname

    DECLARE @IxID INT

    -- Loop through all indexes

    OPEN cIX

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FGName

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    DECLARE @IXSQL NVARCHAR(4000)

    SET @IXSQL = 'CREATE '

    -- Check if the index is unique

    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)

    SET @IXSQL = @IXSQL + 'UNIQUE '

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)

    SET @IXSQL = @IXSQL + 'CLUSTERED '

    SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

    -- Get all columns of the index

    DECLARE cIxColumn CURSOR FOR

    SELECT SC.Name

    FROM sys.index_columns IC

    JOIN sys.columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID

    WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID

    ORDER BY IC.key_ordinal

    DECLARE @IxColumn sysname

    DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

    -- Loop throug all columns of the index and append them to the CREATE statement

    OPEN cIxColumn

    FETCH NEXT FROM cIxColumn INTO @IxColumn

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    IF (@IxFirstColumn = 1)

    SET @IxFirstColumn = 0

    ELSE

    SET @IXSQL = @IXSQL + ', '

    SET @IXSQL = @IXSQL + @IxColumn

    FETCH NEXT FROM cIxColumn INTO @IxColumn

    END

    CLOSE cIxColumn

    DEALLOCATE cIxColumn

    SET @IXSQL = @IXSQL + ')' + 'on '+ @FGName

    -- Print out the CREATE statement for the index

    PRINT @IXSQL

    PRINT 'GO'

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @FGName

    END

    CLOSE cIX

    DEALLOCATE cIX

  • I've updated the code so that it will include both FileGroups and any INCLUDE columns.

    Also made it a stored procedure with the option to include existance test and DROP before create.

    --USE myDB

    GO

    IF NOT EXISTS(SELECT 1

    FROM INFORMATION_SCHEMA.ROUTINES

    WHERE ROUTINE_NAME = 'INFGenerateIndexesScript'

    AND ROUTINE_TYPE = N'PROCEDURE')

    BEGIN

    EXEC ('CREATE PROCEDURE [dbo].[INFGenerateIndexesScript] AS BEGIN SELECT 1 END')

    END

    GO

    GO

    -- **********************************************************************

    -- Sample Usage: EXEC INFGenerateIndexesScript 1, 0

    -- $Revision: 1.2 $

    -- **********************************************************************

    ALTER PROCEDURE INFGenerateIndexesScript

    (

    @IncludeFileGroup bit = 1,

    @IncludeDrop bit = 1

    )

    AS

    BEGIN

    -- Get all existing indexes, but NOT the primary keys

    DECLARE Indexes_cursor CURSOR

    FOR SELECT Object_name(SI.Object_Id) TableName,

    SI.Object_Id TableId,

    SI.[Name] IndexName,

    SI.Index_ID IndexId,

    FG.[Name] FileGroupName

    FROM sys.indexes SI

    LEFT JOIN sys.filegroups FG

    ON SI.data_space_id = FG.data_space_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 @TableName sysname

    DECLARE @TableId int

    DECLARE @IndexName sysname

    DECLARE @FileGroupName sysname

    DECLARE @IndexId 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 @TableName, @TableId, @IndexName, @IndexId, @FileGroupName

    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 [' + @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 [' + @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 @Desc = 1

    BEGIN

    SET @sCreateSql = @sCreateSql + ' DESC'

    END

    ELSE

    BEGIN

    SET @sCreateSql = @sCreateSql + ' ASC'

    END

    --END IF

    FETCH NEXT

    FROM IndexColumns_cursor

    INTO @IxColumn, @IxIncl, @Desc

    END

    --END WHILE

    CLOSE IndexColumns_cursor

    DEALLOCATE IndexColumns_cursor

    IF @IncludeFileGroup = 1

    BEGIN

    SET @sCreateSql = @sCreateSql + @NewLine + ') ON ['+ @FileGroupName + ']' + @NewLine

    END

    ELSE

    BEGIN

    SET @sCreateSql = @sCreateSql + @NewLine + ')' + @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 @TableName, @TableId, @IndexName, @IndexId, @FileGroupName

    END

    --END WHILE

    CLOSE Indexes_cursor

    DEALLOCATE Indexes_cursor

    END

    GO

  • Hey

    I just don’t get it. I run the script in a Database that has tables with indexes but after the script ends no results are showing.

    Can anyone help me telling me what I’m doing wrong?

  • forerolui (1/29/2010)


    Hey

    I just don’t get it. I run the script in a Database that has tables with indexes but after the script ends no results are showing.

    Can anyone help me telling me what I’m doing wrong?

    You need to break the script down to individual commands, to work out where the error is occurring.

    http://90.212.51.111 domain

  • But I´m not geting error messages, I´m just geting a blank result page.

  • forerolui (2/1/2010)


    But I´m not geting error messages, I´m just geting a blank result page.

    Are you running the script that creates the stored procedure which when run will create the script?

    If so you would need to run the stored procedure after you have installed it.

  • Thanks for the update. It's proved to be useful many times for me.

    "Beliefs" get in the way of learning.

Viewing 15 posts - 1 through 15 (of 47 total)

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