SQL Server 2005: Script all Indexes

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Oxygen » SQL Server 2005: Script all Indexes

43 posts, Page 4 of 5

«««1 2 3 4 5 »»

SQL Server 2005: Script all Indexes

Rate Topic

Display Mode

Topic Options

Author

Message

frobert-982342

Posted Friday, May 18, 2012 5:30 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 18, 2012 5:29 PM
Points: 1, Visits: 6

nvrm my post, i was not using the latest version!

And thx for the nice script!

Post #1302911

zashah 83615

Posted Thursday, December 06, 2012 8:05 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, February 13, 2013 7:43 AM
Points: 1, Visits: 15

Very handy script, thanks for your efforts.

Ali

Post #1393542

Jeff Moden

Posted Tuesday, January 22, 2013 8:57 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 33,111, Visits: 27,037

Caveate Emptor. The latest listed script misses unique (clustered or not) and clustered indexes.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1410086

Jonathan AC Roberts

Posted Tuesday, January 22, 2013 9:36 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 321, Visits: 1,219

Jeff,
The following lines in the SP exclude the primary key and any indexes that are created via adding a unique constraint rather than adding a unique index.
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0

Post #1410116

Resender

Posted Friday, February 08, 2013 5:30 AM

SSC Veteran

Group: General Forum Members
Last Login: Wednesday, March 27, 2013 8:55 AM
Points: 289, Visits: 683

Does this work and how

Post #1417625

Jeff Moden

Posted Friday, February 08, 2013 8:07 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 9:01 PM
Points: 33,111, Visits: 27,037

Jonathan AC Roberts (1/22/2013)

Sorry for the late feedback. Yes, I agree. I just can't imagine why anyone would do such a thing in a script titled "Script ALL Indexes".

--Jeff Moden

Post #1417725

Jonathan AC Roberts

Posted Friday, February 08, 2013 8:38 AM

Old Hand

Group: General Forum Members
Last Login: Today @ 2:23 AM
Points: 321, Visits: 1,219

Jeff Moden (2/8/2013)

Sorry for the late feedback. Yes, I agree. I just can't imagine why anyone would do such a thing in a script titled "Script ALL Indexes".

Jeff, Yes a valid point, I've amended the procedure so it takes a table-name-pattern parameter so you can enter a table name with wildcards to generate index script for the only tables you want.

IF NOT EXISTS(SELECT NULL
                FROM INFORMATION_SCHEMA.SCHEMATA
               WHERE SCHEMA_NAME = 'utils'
                 AND SCHEMA_OWNER = 'dbo')
BEGIN
    EXEC('CREATE SCHEMA utils AUTHORIZATION dbo')
END
GO

IF NOT EXISTS(SELECT NULL
                FROM INFORMATION_SCHEMA.ROUTINES
               WHERE ROUTINE_NAME = 'GenerateIndexesScript'
                 AND ROUTINE_TYPE = N'PROCEDURE')
BEGIN
    EXEC ('CREATE PROCEDURE [utils].[GenerateIndexesScript] AS BEGIN SELECT 1 END')
END
GO

/*
Adapted from http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx (by Jonathan AC Roberts. )

Modifications 10/06/2010 By R.Doering - http://sqlsolace.blogspot.com

        1) Changed Schema of routine to Utils
        2) Changed Name from INFGenerateIndexesScript to GenerateIndexesScript
        3) Added Schemas to script
        4) Reformatted for clarity

--  Usage: EXEC utils.GenerateIndexesScript '%O%', 1, 0, 0
Sample call utils.GenerateIndexesScript

Modifications 2012-May-04 R. Gosling
1) Added in the Schema name to table name

*/
ALTER PROCEDURE utils.GenerateIndexesScript
(
    @TableNamePattern sysname = '%',
    @IncludeFileGroup  bit = 1,
    @IncludeDrop       bit = 1,
    @IncludeFillFactor bit = 1
)
AS

BEGIN
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor cursor
        FOR SELECT SC.Name          AS SchemaName,
                   SO.Name          AS TableName,
                   SI.OBJECT_ID     AS TableId,
                   SI.[Name]        AS IndexName,
                   SI.Index_ID      AS IndexId,
                   FG.[Name]        AS FileGroupName,
                   CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
             INNER JOIN sys.objects SO
                     ON SI.OBJECT_ID = SO.OBJECT_ID
             INNER JOIN sys.schemas SC
                     ON SC.schema_id = SO.schema_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
               AND OBJECT_NAME(SI.OBJECT_ID) LIKE @TableNamePattern
             ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

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

    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 [' + @SchemaName + '].[' + @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 [' + @SchemaName + '].[' + @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.[is_included_column],
                              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 @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                            --END IF
                        END
                    --END IF
                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
                END
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @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 @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END
    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor

END
GO

Post #1417744

philipp.ketelhut

Posted Friday, May 31, 2013 10:39 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 10:34 AM
Points: 2, Visits: 0

Hy foks,
found you script just a few min ago.
I added some suff to it!
Now available @InclucdeCheck as well as @InclucdeTryCatch BigGrin

@IncludeCheck works only if @IncludeDrop is equal to 0! Since the drop makes a check unnecessarily!

ALTER PROCEDURE utils.GenerateIndexesScript
(
    @TableNamePattern sysname = '%',
    @IncludeFileGroup  bit = 1,
    @IncludeDrop       bit = 1,
    @IncludeFillFactor bit = 0,
	@InclucdeCheck  bit = 1,
	@InclucdeTryCatch  bit = 1
)
AS

BEGIN
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor cursor
        FOR SELECT SC.Name          AS SchemaName,
                   SO.Name          AS TableName,
                   SI.OBJECT_ID     AS TableId,
                   SI.[Name]        AS IndexName,
                   SI.Index_ID      AS IndexId,
                   FG.[Name]        AS FileGroupName,
                   CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
             INNER JOIN sys.objects SO
                     ON SI.OBJECT_ID = SO.OBJECT_ID
             INNER JOIN sys.schemas SC
                     ON SC.schema_id = SO.schema_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
               AND OBJECT_NAME(SI.OBJECT_ID) LIKE @TableNamePattern
             ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

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

    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 [' + @SchemaName + '].[' + @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 [' + @SchemaName + '].[' + @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.[is_included_column],
                              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 @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                            --END IF
                        END
                    --END IF
                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
                END
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'

            IF @IncludeDrop = 1
                BEGIN
                    PRINT @sDropSql
                    PRINT 'GO'
                END
            --END IF
			--- Insert by PK 31.05.2013
			If @IncludeDrop=0 and @InclucdeCheck=1
				Begin
					Print'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = ''' +@IndexName+''' AND object_id = OBJECT_ID('''+@TableName+'''))'
					Print'Begin'
				End
			if @InclucdeTryCatch=1
				Begin
					Print 'Begin Try'
				end
            PRINT @sCreateSql
			--- Insert by PK 31.05.2013
			if @InclucdeTryCatch=1
			Begin
				Print 'End Try'
				Print 'Begin Catch'
				Print 'RAISERROR (''The Index ' + @IndexName + ' on Table '+@TableName+' could not be created'', 11,1)'
				Print 'End Catch'
			End
			If @IncludeDrop=0 and @InclucdeCheck=1
				Begin
					Print'End'
				End
            PRINT 'GO' + @NewLine  + @NewLine

            FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END
    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor

END
GO

Post #1458795

philipp.ketelhut

Posted Friday, May 31, 2013 10:48 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 10:34 AM
Points: 2, Visits: 0

For all how love copy paste and dont know what to do, here is a skript that works without calling a stored procedure

BEGIN
Declare    @TableNamePattern sysname = '%';
Declare    @IncludeFileGroup  bit = 1;
Declare    @IncludeDrop       bit = 1;
Declare    @IncludeFillFactor bit = 0;
Declare	@InclucdeCheck  bit = 1;
Declare	@InclucdeTryCatch  bit = 1;
    -- Get all existing indexes, but NOT the primary keys
    DECLARE Indexes_cursor cursor
        FOR SELECT SC.Name          AS SchemaName,
                   SO.Name          AS TableName,
                   SI.OBJECT_ID     AS TableId,
                   SI.[Name]        AS IndexName,
                   SI.Index_ID      AS IndexId,
                   FG.[Name]        AS FileGroupName,
                   CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END  Fill_Factor
              FROM sys.indexes SI
              LEFT JOIN sys.filegroups FG
                     ON SI.data_space_id = FG.data_space_id
             INNER JOIN sys.objects SO
                     ON SI.OBJECT_ID = SO.OBJECT_ID
             INNER JOIN sys.schemas SC
                     ON SC.schema_id = SO.schema_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
               AND OBJECT_NAME(SI.OBJECT_ID) LIKE @TableNamePattern
             ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID

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

    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 [' + @SchemaName + '].[' + @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 [' + @SchemaName + '].[' + @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.[is_included_column],
                              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 @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                            --END IF
                        END
                    --END IF
                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END
            --END WHILE
            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
                END
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'

            IF @IncludeDrop = 1
                BEGIN
                    PRINT @sDropSql
                    PRINT 'GO'
                END
            --END IF
			--- Insert by PK 31.05.2013
			If @IncludeDrop=0 and @InclucdeCheck=1
				Begin
					Print'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE name = ''' +@IndexName+''' AND object_id = OBJECT_ID('''+@TableName+'''))'
					Print'Begin'
				End
			if @InclucdeTryCatch=1
				Begin
					Print 'Begin Try'
				end
            PRINT @sCreateSql
			--- Insert by PK 31.05.2013
			if @InclucdeTryCatch=1
			Begin
				Print 'End Try'
				Print 'Begin Catch'
				Print 'RAISERROR (''The Index ' + @IndexName + ' on Table '+@TableName+' could not be created'', 11,1)'
				Print 'End Catch'
			End
			If @IncludeDrop=0 and @InclucdeCheck=1
				Begin
					Print'End'
				End
            PRINT 'GO' + @NewLine  + @NewLine

            FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END
    --END WHILE
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor

END

Post #1458801

niquem1974

Posted Tuesday, June 11, 2013 12:40 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, June 14, 2013 2:59 AM
Points: 4, Visits: 157

....And the ball keeps rolling

I found this solution and tinkered with it for my own needs

I have a Utils DB that I call SPs from therefore I need to be able to execute from another Db and be able to add the indicies to another Db or linked server .
This script will run on a single table at a time (as I plan to call this as part of an overarching process that may or may not need to create Indices)

Essentially I have added the ability to:

Execute the processes via setting @Exe param
Execute and get the statements to run on another Db/Server via the @ToStr param

I have removed the ability to:
Run on all tables in the given DB

Hope that helps someone out of a hole
Enjoy

** I apologise that the formatting appears to have gone a bit squiffy on copy & paste....I cannae be arsed to go through and sort it out but Im sure you all get picture :)


CREATE PROCEDURE sp_util_GenerateIndexesScript(@FrmStr varchar(316), @ToStr varchar(316) = NULL, @Exe BIT = NULL,

    @IncludeFileGroup	bit = 1,
    @IncludeDrop	bit = 1,
    @IncludeFillFactor	bit = 0,
    @IncludeCheck	bit = 1,
    @IncludeTryCatch	bit = 0
)
AS
/*
This Sp will allow you to script the indices from a table for recreation

@FromTbl	= Can be a fully qualified extended name as you can run into a different DB on a differing server
@ToTbl	= Can be left blank or you can pass a partially or fully qualified name 
@Exe		= Can be left blank or marked as 1. This will mean that the script executes rather than just outputs a script

Examples:
EXEC sp_util_GenerateIndexesScript 'Db1.dbo.TblData_Emails', 'LinkedServ1.Db2.dbo.TblData_Emails' - This will create the index from the first local table on the remote server

*/
DECLARE 
@CurTbl		varchar(max),
@RunSql		varchar(max),
@ICSql		nvarchar(max),
@UseServ	        varchar(200),
@UseServCls       varchar(2),
@FrmServ	        varchar(30),
@FrmDb		varchar(128),
@FrmSch		varchar(30),
@FrmTbl		varchar(128),
@ToServ		varchar(30),
@ToDb		varchar(128),
@ToSch		varchar(30),
@ToTbl		varchar(128)
/*	
--Test settings
	,@FrmStr		varchar(316),
	@ToStr		varchar(316),
	@Exe			bit = 1,
    @IncludeFileGroup	bit = 1,
    @IncludeDrop		bit = 1,
    @IncludeFillFactor	bit = 0,
    @IncludeCheck	bit = 1,
    @IncludeTryCatch	bit = 0
Set @FrmStr = 'DB1.dbo.TblData_Emails'
Set @ToStr = --'Server01.DB2.dbo.TblData_Emails'
'DB3.dbo.TblData_Emails'
*/
	
Set @FrmServ	= CASE WHEN PARSENAME(@FrmStr,4)IS NULL THEN ''		ELSE PARSENAME(@FrmStr,4)+'.' END
Set @FrmDB	= CASE WHEN PARSENAME(@FrmStr,3)IS NULL THEN ''		ELSE PARSENAME(@FrmStr,3)+'.' END
Set @FrmSch	= CASE WHEN PARSENAME(@FrmStr,2)IS NULL THEN ''		ELSE PARSENAME(@FrmStr,2)+'.' END
Set @FrmTbl	= PARSENAME(@FrmStr,1)

Set @ToServ	= CASE WHEN PARSENAME(@ToStr,4)IS NULL THEN @FrmServ	ELSE PARSENAME(@ToStr,4)+'.' END
Set @ToDB		= CASE WHEN PARSENAME(@ToStr,3)IS NULL THEN @FrmDb		ELSE PARSENAME(@ToStr,3)+'.' END
Set @ToSch	= CASE WHEN PARSENAME(@ToStr,2)IS NULL THEN @FrmSch		ELSE PARSENAME(@ToStr,2)+'.' END
Set @ToTbl		= CASE WHEN PARSENAME(@ToStr,1)IS NULL THEN @FrmTbl		ELSE PARSENAME(@ToStr,1)	 END

Set @Exe		= CASE WHEN @Exe <> 1					THEN 0 ELSE 1 END
Set @RunSql	= ''
Set @UseServ	= 'EXECUTE ' +  @ToServ + REPLACE(@ToDb, '.','')+'.[dbo].[sp_executesql] N'''

BEGIN
-- Get all existing indexes, but NOT the primary keys
 Set @CurTbl = 
				' 
				  DECLARE Indexes_cursor cursor
					FOR
						SELECT  SC.Name          AS SchemaName,
								SO.Name          AS TableName,
								SI.OBJECT_ID     AS TableId,
								SI.[Name]        AS IndexName,
								SI.Index_ID      AS IndexId,
								FG.[Name]        AS FileGroupName,
								CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END as Fill_Factor
								FROM '		+ @FrmServ + @FrmDb + 'sys.indexes AS SI
								LEFT JOIN ' + @FrmServ + @FrmDb + 'sys.filegroups AS FG
						ON SI.data_space_id = FG.data_space_id
						INNER JOIN ' + @FrmServ + @FrmDb + 'sys.objects	AS SO
						ON SI.OBJECT_ID = SO.OBJECT_ID
						INNER JOIN ' + @FrmServ + @FrmDb + 'sys.schemas	AS SC
						ON SC.schema_id = SO.schema_id
						WHERE SO.Type = ''U''
						  AND SI.[Name] IS NOT NULL
						  AND SI.is_primary_key = 0
						  AND SI.is_unique_constraint = 0
						  AND SI.is_disabled = 0
						  --AND INDEXPROPERTY(SI.OBJECT_ID, SI.[Name], ''IsStatistics'') = 0
						  AND SO.Name =''' + @FrmTbl + '''
						  ORDER BY OBJECT_NAME(SI.OBJECT_ID), SI.Index_ID
				'
Exec  (@CurTbl)

    DECLARE @SchemaName     sysname
    DECLARE @TableName      sysname
    DECLARE @TableId        int
    DECLARE @IndexName      sysname
    DECLARE @FileGroupName  sysname
    DECLARE @IndexId        int
    DECLARE @FillFactor     int
    DECLARE @NewLine		nvarchar(4000)
    DECLARE @Tab			nvarchar(4000)
    
    SET @NewLine = char(13) + char(10)
    SET @Tab = SPACE(4)

-- Loop through all indexes
    OPEN Indexes_cursor

    FETCH NEXT
     FROM Indexes_cursor
     INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor

    WHILE (@@FETCH_STATUS = 0)
        BEGIN

            DECLARE @sIndexDesc nvarchar(4000)
            DECLARE @sCreateSql nvarchar(4000)
            DECLARE @sDropSql   nvarchar(4000)

            SET @sIndexDesc = '-- Create Index ' + @IndexName + ' on table ' + @ToServ + @ToDb + '[' + @SchemaName + '].[' + @TableName + ']'
            SET @sDropSql = 
							'IF EXISTS(SELECT 1'	+ @NewLine
                          + '          FROM '		+ @ToDb + '.sysindexes si' + @NewLine
                          + '          INNER JOIN ' + @ToDb + '.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 ' + @ToDb + '[' + @SchemaName + '].[' + @TableName + ']' + @NewLine
                          + 'END' 
			
			SET @RunSql		= ''
            SET @sCreateSql = 'CREATE '

-- Check if the index is unique
            IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'UNIQUE '
                END
                
            -- Check if the index is clustered
            IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'CLUSTERED '
                END

            SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON ' + @ToDB + '[' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine

-- Get all columns of the index
 Set @ICSql = 
 '
            DECLARE IndexColumns_cursor CURSOR
                FOR SELECT SC.[Name],
                           IC.[is_included_column],
                           IC.is_descending_key
                      FROM '	 + @FrmServ + @FrmDb + 'sys.index_columns as IC
                     INNER JOIN '+ @FrmServ + @FrmDb + 'sys.columns as SC
                             ON IC.OBJECT_ID = SC.OBJECT_ID
                            AND IC.Column_ID = SC.Column_ID
                     WHERE IC.OBJECT_ID = @TId
                       AND Index_ID = @IId
                     ORDER BY IC.[is_included_column],
                              IC.key_ordinal
'
Exec Sp_ExecuteSQL @ICSql, N'@TId as integer, @IId as integer', @TId = @TableId, @IId = @IndexId

            DECLARE @IxColumn		sysname
            DECLARE @IxIncl			bit
            DECLARE @Desc			bit
            DECLARE @IxIsIncl		bit
            DECLARE @IxFirstColumn  bit
            
            SET @IxIsIncl = 0
            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

                    SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
-- check if ASC or DESC
                    IF @IxIsIncl = 0
                        BEGIN
                            IF @Desc = 1
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' DESC'
                                END
                            ELSE
                                BEGIN
                                    SET @sCreateSql = @sCreateSql + ' ASC'
                                END
                        END

                    FETCH NEXT
                     FROM IndexColumns_cursor
                     INTO @IxColumn, @IxIncl, @Desc
                END

            CLOSE IndexColumns_cursor
            DEALLOCATE IndexColumns_cursor

            SET @sCreateSql = @sCreateSql + @NewLine + ') '

            IF @IncludeFillFactor = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + CAST(@FillFactor AS varchar(13)) + ')' + @NewLine
                END
            --END IF

            IF @IncludeFileGroup = 1
                BEGIN
                    SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
                END
            ELSE
                BEGIN
                    SET @sCreateSql = @sCreateSql + @NewLine
                END
            --END IF

            PRINT '-- **********************************************************************'
            PRINT @sIndexDesc
            PRINT '-- **********************************************************************'

            IF @IncludeDrop <> 1
              	BEGIN
					Set     @sDropSql = ''
				END

			If @IncludeDrop=0 and @IncludeCheck=1
				Begin
					Set @RunSQL = 'IF NOT EXISTS(SELECT * FROM ' + @FrmServ + @FrmDb + 'sys.indexes WHERE name = ''' +@IndexName+''' AND object_id = OBJECT_ID('''+@TableName+'''))'
								  + @NewLine +
								  'Begin'
				End
			if @IncludeTryCatch=1
				Begin
					Set @RunSQL = @RunSQL  + @NewLine + 
						'Begin Try'
				end
--Main Statement
Set @RunSQL = @RunSQL  + @NewLine + @sCreateSql

			if @IncludeTryCatch=1
			Begin
					Set	@RunSQL = @RunSQL	+ @NewLine + 
							'End Try'		+ @NewLine +
							'Begin Catch'	+ @NewLine +
							'	RAISERROR (''The Index ' + @IndexName + ' on Table ' + @ToServ + @ToDb + @ToSch +@TableName+' could not be created'', 11,1)' + @NewLine +
							'End Catch'
			End
			If @IncludeDrop=0 and @IncludeCheck=1
				Begin
					Set	@RunSQL = @RunSQL  
								+ @NewLine +
								  'End'
				End

-- Update the strings if they are to go to another server
IF @ToServ <> @FrmServ  
Begin
	Set  @sDropSql = @UseServ + REPLACE(@sDropSql, '''','''''') + '''' + @NewLine
	Set  @RunSQL   = @UseServ + REPLACE(@RunSQL, '''','''''')   + '''' + @NewLine
End  
        
--Print the statements
PRINT @sDropSql
PRINT @RunSQL

--See if you want to Execute the scripts
IF @Exe = 1 
Begin
	Exec (@sDropSql)
	Exec (@RunSql)
end

--Move to the next record
		FETCH NEXT
             FROM Indexes_cursor
             INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
        END
        
--At the end Clean up
    CLOSE Indexes_cursor
    DEALLOCATE Indexes_cursor

END
GO

Post #1462327

« Prev Topic | Next Topic »

43 posts, Page 4 of 5

«««1 2 3 4 5 »»

Permissions