SQL Server 2005: Script all Indexes

  • Oxygen

    SSC-Addicted

    Points: 469

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

  • Anthony Robinson

    SSC Veteran

    Points: 240

    You forgot to declare @PKSQL

  • neil-560592

    Default port

    Points: 1470

    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

  • philipp.kanne

    SSC Journeyman

    Points: 93

    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

  • Robert Frasca

    SSCertifiable

    Points: 5935

    Didn't declare @tablename.

    "Beliefs" get in the way of learning.

  • stephenc-324995

    SSC Enthusiast

    Points: 125

    It didn't cater asc or desc order

  • jeffryfranksjunk

    SSC Enthusiast

    Points: 161

    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.

  • Scott Hannon-278869

    SSC Enthusiast

    Points: 127

    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

  • HARVINDER SINGH-301803

    SSC Enthusiast

    Points: 107

    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

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    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

  • forerolui

    SSC-Addicted

    Points: 448

    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?

  • neil-560592

    Default port

    Points: 1470

    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.

  • forerolui

    SSC-Addicted

    Points: 448

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

  • Jonathan AC Roberts

    SSCoach

    Points: 16459

    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.

  • Robert Frasca

    SSCertifiable

    Points: 5935

    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