Create Script of all indexes in a Database

  • Could you please help me out how to script out all 'indexes create script' from user database?

    Thank you in advance:-)

  • -- Script out indexes completely, including both PK's and regular indexes, each clustered or nonclustered.

    -- DOES NOT HANDLE COMPRESSION; that's ok, since 2008 R2 RTM benchmarking shows it's faster and results in smaller indexes to insert uncompressed and then compress later

    -- HARDCODES [dbo] schema (i.e. it doesn't say [JohnDoe].

    , changing that to [dbo].

    -- originally from http://www.sqlservercentral.com/Forums/Topic961088-2753-2.aspx

    SET NOCOUNT ON

    DECLARE

    @idxTableName SYSNAME,

    @idxTableID INT,

    @idxname SYSNAME,

    @idxid INT,

    @colCount INT,

    @colCountMinusIncludedColumns INT,

    @IxColumn SYSNAME,

    @IxFirstColumn BIT,

    @ColumnIDInTable INT,

    @ColumnIDInIndex INT,

    @IsIncludedColumn INT,

    @sIncludeCols VARCHAR(MAX),

    @sIndexCols VARCHAR(MAX),

    @sSQL VARCHAR(MAX),

    @sParamSQL VARCHAR(MAX),

    @sFilterSQL VARCHAR(MAX),

    @location SYSNAME,

    @IndexCount INT,

    @CurrentIndex INT,

    @CurrentCol INT,

    @Name VARCHAR(128),

    @IsPrimaryKey TINYINT,

    @Fillfactor INT,

    @FilterDefinition VARCHAR(MAX),

    @IsClustered BIT -- used solely for putting information into the result table

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexSQL]'))

    DROP TABLE [dbo].[#IndexSQL]

    CREATE TABLE #IndexSQL

    ( TableName VARCHAR(128) NOT NULL

    ,IndexName VARCHAR(128) NOT NULL

    ,IsClustered BIT NOT NULL

    ,IsPrimaryKey BIT NOT NULL

    ,IndexCreateSQL VARCHAR(max) NOT NULL

    )

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#IndexListing]'))

    DROP TABLE [dbo].[#IndexListing]

    CREATE TABLE #IndexListing

    (

    [IndexListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    [TableName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ObjectID] INT NOT NULL,

    [IndexName] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [IndexID] INT NOT NULL,

    [IsPrimaryKey] TINYINT NOT NULL,

    [FillFactor] INT,

    [FilterDefinition] NVARCHAR(MAX) NULL

    )

    IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE id = object_id(N'[tempdb].[dbo].[#ColumnListing]'))

    DROP TABLE [dbo].[#ColumnListing]

    CREATE TABLE #ColumnListing

    (

    [ColumnListingID] INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    [ColumnIDInTable] INT NOT NULL,

    [Name] SYSNAME COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    [ColumnIDInIndex] INT NOT NULL,

    [IsIncludedColumn] BIT NULL

    )

    INSERT INTO #IndexListing( [TableName], [ObjectID], [IndexName], [IndexID], [IsPrimaryKey], [FILLFACTOR], [FilterDefinition] )

    SELECT OBJECT_NAME(si.object_id), si.object_id, si.name, si.index_id, si.Is_Primary_Key, si.Fill_Factor, si.filter_definition

    FROM sys.indexes si

    LEFT OUTER 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

    SELECT @IndexCount = @@ROWCOUNT, @CurrentIndex = 1

    WHILE @CurrentIndex <= @IndexCount

    BEGIN

    SELECT @idxTableName = [TableName],

    @idxTableID = [ObjectID],

    @idxname = [IndexName],

    @idxid = [IndexID],

    @IsPrimaryKey = [IsPrimaryKey],

    @FillFactor = [FILLFACTOR],

    @FilterDefinition = [FilterDefinition]

    FROM #IndexListing

    WHERE [IndexListingID] = @CurrentIndex

    -- So - it is either an index or a constraint

    -- Check if the index is unique

    IF (@IsPrimaryKey = 1)

    BEGIN

    SET @sSQL = 'ALTER TABLE [dbo].[' + @idxTableName + '] ADD CONSTRAINT [' + @idxname + '] PRIMARY KEY '

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 0)

    BEGIN

    SET @sSQL = @sSQL + 'NON'

    SET @IsClustered = 0

    END

    ELSE

    BEGIN

    SET @IsClustered = 1

    END

    SET @sSQL = @sSQL + 'CLUSTERED' + CHAR(13) + '(' + CHAR(13)

    END

    ELSE

    BEGIN

    SET @sSQL = 'CREATE '

    -- Check if the index is unique

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsUnique') = 1)

    BEGIN

    SET @sSQL = @sSQL + 'UNIQUE '

    END

    -- Check if the index is clustered

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsClustered') = 1)

    BEGIN

    SET @sSQL = @sSQL + 'CLUSTERED '

    SET @IsClustered = 1

    END

    ELSE

    BEGIN

    SET @IsClustered = 0

    END

    SELECT

    @sSQL = @sSQL + 'INDEX [' + @idxname + '] ON [dbo].[' + @idxTableName + ']' + CHAR(13) + '(' + CHAR(13),

    @colCount = 0,

    @colCountMinusIncludedColumns = 0

    END

    -- Get the nuthe mber of cols in the index

    SELECT @colCount = COUNT(*),

    @colCountMinusIncludedColumns = SUM(CASE ic.is_included_column WHEN 0 THEN 1 ELSE 0 END)

    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

    -- 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

    INSERT INTO #ColumnListing( [ColumnIDInTable], [Name], [ColumnIDInIndex],[IsIncludedColumn] )

    SELECT sc.column_id, sc.name, ic.index_column_id, ic.is_included_column

    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

    IF @@ROWCOUNT > 0

    BEGIN

    SELECT @CurrentCol = 1

    SELECT @IxFirstColumn = 1, @sIncludeCols = '', @sIndexCols = ''

    WHILE @CurrentCol <= @ColCount

    BEGIN

    SELECT @ColumnIDInTable = ColumnIDInTable,

    @Name = Name,

    @ColumnIDInIndex = ColumnIDInIndex,

    @IsIncludedColumn = IsIncludedColumn

    FROM #ColumnListing

    WHERE [ColumnListingID] = @CurrentCol

    IF @IsIncludedColumn = 0

    BEGIN

    SELECT @sIndexCols = CHAR(9) + @sIndexCols + '[' + @Name + '] '

    -- Check the sort order of the index cols ????????

    IF (INDEXKEY_PROPERTY (@idxTableID,@idxid,@ColumnIDInIndex,'IsDescending')) = 0

    BEGIN

    SET @sIndexCols = @sIndexCols + ' ASC '

    END

    ELSE

    BEGIN

    SET @sIndexCols = @sIndexCols + ' DESC '

    END

    IF @CurrentCol < @colCountMinusIncludedColumns

    BEGIN

    SET @sIndexCols = @sIndexCols + ', '

    END

    END

    ELSE

    BEGIN

    -- Check for any include columns

    IF LEN(@sIncludeCols) > 0

    BEGIN

    SET @sIncludeCols = @sIncludeCols + ','

    END

    SELECT @sIncludeCols = @sIncludeCols + '[' + @Name + ']'

    END

    SET @CurrentCol = @CurrentCol + 1

    END

    TRUNCATE TABLE #ColumnListing

    --append to the result

    IF LEN(@sIncludeCols) > 0

    SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') ' + ' INCLUDE ( ' + @sIncludeCols + ' ) '

    ELSE

    SET @sIndexCols = @sSQL + @sIndexCols + CHAR(13) + ') '

    -- Add filtering

    IF @FilterDefinition IS NOT NULL

    SET @sFilterSQL = ' WHERE ' + @FilterDefinition + ' ' + CHAR(13)

    ELSE

    SET @sFilterSQL = ''

    -- Build the options

    SET @sParamSQL = 'WITH ( PAD_INDEX = '

    IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPadIndex') = 1

    SET @sParamSQL = @sParamSQL + 'ON,'

    ELSE

    SET @sParamSQL = @sParamSQL + 'OFF,'

    SET @sParamSQL = @sParamSQL + ' ALLOW_PAGE_LOCKS = '

    IF INDEXPROPERTY(@idxTableID, @idxname, 'IsPageLockDisallowed') = 0

    SET @sParamSQL = @sParamSQL + 'ON,'

    ELSE

    SET @sParamSQL = @sParamSQL + 'OFF,'

    SET @sParamSQL = @sParamSQL + ' ALLOW_ROW_LOCKS = '

    IF INDEXPROPERTY(@idxTableID, @idxname, 'IsRowLockDisallowed') = 0

    SET @sParamSQL = @sParamSQL + 'ON,'

    ELSE

    SET @sParamSQL = @sParamSQL + 'OFF,'

    SET @sParamSQL = @sParamSQL + ' STATISTICS_NORECOMPUTE = '

    -- THIS DOES NOT WORK PROPERLY; IsStatistics only says what generated the last set, not what it was set to do.

    IF (INDEXPROPERTY(@idxTableID, @idxname, 'IsStatistics') = 1)

    SET @sParamSQL = @sParamSQL + 'ON'

    ELSE

    SET @sParamSQL = @sParamSQL + 'OFF'

    -- Fillfactor 0 is actually not a valid percentage on SQL 2008 R2

    IF ISNULL( @FillFactor, 90 ) <> 0

    SET @sParamSQL = @sParamSQL + ' ,FILLFACTOR = ' + CAST( ISNULL( @FillFactor, 90 ) AS VARCHAR(3) )

    IF (@IsPrimaryKey = 1) -- DROP_EXISTING isn't valid for PK's

    BEGIN

    SET @sParamSQL = @sParamSQL + ' ) '

    END

    ELSE

    BEGIN

    SET @sParamSQL = @sParamSQL + ' ,DROP_EXISTING = ON ) '

    END

    SET @sSQL = @sIndexCols + CHAR(13) + @sFilterSQL + CHAR(13) + @sParamSQL

    -- 2008 R2 allows ON [filegroup] for primary keys as well, negating the old "IF THE INDEX IS NOT A PRIMARY KEY - ADD THIS - ELSE DO NOT" IsPrimaryKey IF statement

    SET @sSQL = @sSQL + ' ON [' + @location + ']'

    --PRINT @sIndexCols + CHAR(13)

    INSERT INTO #IndexSQL (TableName, IndexName, IsClustered, IsPrimaryKey, IndexCreateSQL) VALUES (@idxTableName, @idxName, @IsClustered, @IsPrimaryKey, @sSQL)

    END

    SET @CurrentIndex = @CurrentIndex + 1

    END

    SELECT * FROM #IndexSQL

    ==========================================================================================================================
    A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

  • here's another version i had slapped together once;

    this is specific to SQL 2008 and above, becuase it's also scripting filtered indexes and include columns;

    i never bothered fleshing out the partitions section of the scripts, but left the code in comments for reference.

    if you run this on SQL 2005, you'll have to change it, as you get errors on the has_filter column and a few other things.

    ;WITH MyStagingData

    AS

    (

    (SELECT

    SCH.schema_id,

    SCH.[name] COLLATE database_default AS SCHEMA_NAME,

    OBJS.[object_id],

    OBJS.[name] COLLATE database_default AS OBJECT_NAME,

    IDX.index_id,

    ISNULL(IDX.[name], '---') COLLATE database_default AS index_name,

    partstatz.Rows,

    partstatz.SizeMB,

    INDEXPROPERTY(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,

    IDX.type,

    IDX.type_desc COLLATE database_default AS type_desc,

    IDX.is_unique,

    IDX.is_primary_key,

    IDX.is_unique_constraint,

    IDX.has_filter,

    IDX.filter_definition,

    ISNULL(Index_Columns.index_columns_key, '---') COLLATE database_default AS index_columns_key,

    ISNULL(Index_Columns.index_columns_include, '---') COLLATE database_default AS index_columns_include,

    IDX.is_padded,

    IDX.fill_factor,

    IDX.ignore_dup_key,

    STATZ.no_recompute ,

    IDX.allow_row_locks,

    IDX.allow_page_locks,

    PARTZ.data_compression_desc

    FROM sys.objects OBJS

    INNER JOIN sys.schemas SCH

    ON OBJS.schema_id = SCH.schema_id

    INNER JOIN sys.indexes IDX

    ON OBJS.[object_id] = IDX.[object_id]

    INNER JOIN (SELECT

    [OBJECT_ID],

    index_id,

    SUM(row_count) AS ROWS,

    CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(NUMERIC(19, 3), 128)) AS SizeMB

    FROM sys.dm_db_partition_stats STATS

    GROUP BY

    [OBJECT_ID],

    index_id) AS partstatz

    ON IDX.[object_id] = partstatz.[object_id]

    AND IDX.index_id = partstatz.index_id

    INNER JOIN sys.partitions PARTZ

    ON IDX.[object_id] = PARTZ.[object_id]

    AND IDX.index_id = PARTZ.index_id

    INNER JOIN sys.stats STATZ

    ON IDX.[object_id] = STATZ.[object_id]

    AND IDX.index_id = STATZ.stats_id

    CROSS APPLY (SELECT

    LEFT(index_columns_key, LEN(index_columns_key) - 1) COLLATE database_default AS index_columns_key,

    LEFT(index_columns_include, LEN(index_columns_include) - 1) COLLATE database_default AS index_columns_include

    FROM (SELECT

    (SELECT

    colz.[name] + CASE WHEN IXCOLS.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' + ' ' COLLATE database_default

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns colz

    ON IXCOLS.column_id = colz.column_id

    AND IXCOLS.[object_id] = colz.[object_id]

    WHERE IXCOLS.is_included_column = 0

    AND IDX.[object_id] = IXCOLS.[object_id]

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY

    key_ordinal

    FOR XML PATH('')) AS index_columns_key,

    (SELECT

    colz.[name] + ',' + ' ' COLLATE database_default

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns colz

    ON IXCOLS.column_id = colz.column_id

    AND IXCOLS.[object_id] = colz.[object_id]

    WHERE IXCOLS.is_included_column = 1

    AND IDX.[object_id] = IXCOLS.[object_id]

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY

    index_column_id

    FOR XML PATH('')) AS index_columns_include) AS Index_Columns) AS Index_Columns)

    )

    SELECT

    'CREATE '

    + CASE WHEN MyStagingData.is_unique = 1 THEN ' UNIQUE' ELSE ' ' END

    + CASE WHEN MyStagingData.type_desc <> 'HEAP' THEN ' ' + MyStagingData.type_desc ELSE ' ' END

    + ' INDEX '

    + quotename(MyStagingData.index_name)

    + ' ON '

    + + quotename(MyStagingData.schema_name)

    +'.'

    + quotename(MyStagingData.object_name)

    + ' (' + MyStagingData.index_columns_key + ')'

    + CASE WHEN MyStagingData.index_columns_include <> '---' THEN ' INCLUDE (' + MyStagingData.index_columns_include + ')' ELSE '' END

    + CASE WHEN MyStagingData.has_filter = 1 THEN ' WHERE ' + MyStagingData.filter_definition ELSE '' END

    + ' WITH '

    + ' PAD_INDEX = ' + CASE WHEN is_padded = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' FILLFACTOR = ' + convert(varchar,MyStagingData.fill_factor) + ','

    + ' IGNORE_DUP_KEY = ' + CASE WHEN ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' STATISTICS_NORECOMPUTE = ' + CASE WHEN no_recompute = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' ALLOW_ROW_LOCKS = ' + CASE WHEN allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' ALLOW_PAGE_LOCKS = ' + CASE WHEN allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' DATA_COMPRESSION = ' + data_compression_desc

    -- + ','

    --+ ' [ ON PARTITIONS ( { <partition_number_expression> | <range> } '

    --+ ' [ , ...n ] ) ] '

    ,* FROM MyStagingData

    WHERE MyStagingData.SCHEMA_NAME <> 'sys'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Hi thanks for quick reply. this script working on sysdatabases only

    I want create user database indexes please provide the script:-)

    Thanks in Adavance:-)

  • AAKR (7/5/2012)


    Hi thanks for quick reply. this script working on sysdatabases only

    I want create user database indexes please provide the script:-)

    Thanks in Adavance:-)

    which script are you talking about?

    did you run the script while in the user database itself or did you run it in master??

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • AAKR (7/5/2012)


    Hi thanks for quick reply. this script working on sysdatabases only

    I want create user database indexes please provide the script:-)

    Thanks in Adavance:-)

    Both scripts provided should do what you are asking.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • I ran this script in Master and one of my user database

    1. When I ran this script in Master database its executing without error

    2. while executing this script in user database its showing following error

    Msg 207, Level 16, State 1, Line 44

    Invalid column name 'object_id'.

    Msg 207, Level 16, State 1, Line 11

    Invalid column name 'Rows'.

  • I was looking for the same script, had a hard drive malfunction(with no backup) and lost my script library. so instead of reinventing the wheel.

    anywho... I am getting the error Invalid column name 'filter_definition'.

    No matter what script I find online that others have written, getting the same invalid column

  • Lynn Hendricks (1/27/2015)


    I was looking for the same script, had a hard drive malfunction(with no backup) and lost my script library. so instead of reinventing the wheel.

    anywho... I am getting the error Invalid column name 'filter_definition'.

    No matter what script I find online that others have written, getting the same invalid column

    if you run this on SQL 2005, you'll have to change it, as you get errors on the has_filter column and a few other things.

    'filter_definition' was added in 2008 and above, so you'd need to modify this to not use any of the filter related columns or compresison related columns when you run this on 2005.

    i just tested this modified version on a 2005 instance:

    ;WITH MyStagingData

    AS

    (

    (SELECT

    SCH.schema_id,

    SCH.[name] COLLATE database_default AS SCHEMA_NAME,

    OBJS.[object_id],

    OBJS.[name] COLLATE database_default AS OBJECT_NAME,

    IDX.index_id,

    ISNULL(IDX.[name], '---') COLLATE database_default AS index_name,

    partstatz.Rows,

    partstatz.SizeMB,

    INDEXPROPERTY(OBJS.[object_id], IDX.[name], 'IndexDepth') AS IndexDepth,

    IDX.type,

    IDX.type_desc COLLATE database_default AS type_desc,

    IDX.is_unique,

    IDX.is_primary_key,

    IDX.is_unique_constraint,

    0 AS has_filter,

    '' AS filter_definition,

    ISNULL(Index_Columns.index_columns_key, '---') COLLATE database_default AS index_columns_key,

    ISNULL(Index_Columns.index_columns_include, '---') COLLATE database_default AS index_columns_include,

    IDX.is_padded,

    IDX.fill_factor,

    IDX.ignore_dup_key,

    STATZ.no_recompute ,

    IDX.allow_row_locks,

    IDX.allow_page_locks,

    '' AS data_compression_desc

    FROM sys.objects OBJS

    INNER JOIN sys.schemas SCH

    ON OBJS.schema_id = SCH.schema_id

    INNER JOIN sys.indexes IDX

    ON OBJS.[object_id] = IDX.[object_id]

    INNER JOIN (SELECT

    [OBJECT_ID],

    index_id,

    SUM(row_count) AS ROWS,

    CONVERT(NUMERIC(19, 3), CONVERT(NUMERIC(19, 3), SUM(in_row_reserved_page_count+lob_reserved_page_count+row_overflow_reserved_page_count))/CONVERT(NUMERIC(19, 3), 128)) AS SizeMB

    FROM sys.dm_db_partition_stats STATS

    GROUP BY

    [OBJECT_ID],

    index_id) AS partstatz

    ON IDX.[object_id] = partstatz.[object_id]

    AND IDX.index_id = partstatz.index_id

    INNER JOIN sys.partitions PARTZ

    ON IDX.[object_id] = PARTZ.[object_id]

    AND IDX.index_id = PARTZ.index_id

    INNER JOIN sys.stats STATZ

    ON IDX.[object_id] = STATZ.[object_id]

    AND IDX.index_id = STATZ.stats_id

    CROSS APPLY (SELECT

    LEFT(index_columns_key, LEN(index_columns_key) - 1) COLLATE database_default AS index_columns_key,

    LEFT(index_columns_include, LEN(index_columns_include) - 1) COLLATE database_default AS index_columns_include

    FROM (SELECT

    (SELECT

    colz.[name] + CASE WHEN IXCOLS.is_descending_key = 1 THEN ' DESC' ELSE ' ASC' END + ',' + ' ' COLLATE database_default

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns colz

    ON IXCOLS.column_id = colz.column_id

    AND IXCOLS.[object_id] = colz.[object_id]

    WHERE IXCOLS.is_included_column = 0

    AND IDX.[object_id] = IXCOLS.[object_id]

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY

    key_ordinal

    FOR XML PATH('')) AS index_columns_key,

    (SELECT

    colz.[name] + ',' + ' ' COLLATE database_default

    FROM sys.index_columns IXCOLS

    INNER JOIN sys.columns colz

    ON IXCOLS.column_id = colz.column_id

    AND IXCOLS.[object_id] = colz.[object_id]

    WHERE IXCOLS.is_included_column = 1

    AND IDX.[object_id] = IXCOLS.[object_id]

    AND IDX.index_id = IXCOLS.index_id

    ORDER BY

    index_column_id

    FOR XML PATH('')) AS index_columns_include) AS Index_Columns) AS Index_Columns)

    )

    SELECT

    'CREATE '

    + CASE WHEN MyStagingData.is_unique = 1 THEN ' UNIQUE' ELSE ' ' END

    + CASE WHEN MyStagingData.type_desc <> 'HEAP' THEN ' ' + MyStagingData.type_desc ELSE ' ' END

    + ' INDEX '

    + quotename(MyStagingData.index_name)

    + ' ON '

    + + quotename(MyStagingData.schema_name)

    +'.'

    + quotename(MyStagingData.object_name)

    + ' (' + MyStagingData.index_columns_key + ')'

    + CASE WHEN MyStagingData.index_columns_include <> '---' THEN ' INCLUDE (' + MyStagingData.index_columns_include + ')' ELSE '' END

    + CASE WHEN MyStagingData.has_filter = 1 THEN ' WHERE ' + MyStagingData.filter_definition ELSE '' END

    + ' WITH '

    + ' PAD_INDEX = ' + CASE WHEN is_padded = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' FILLFACTOR = ' + convert(varchar,MyStagingData.fill_factor) + ','

    + ' IGNORE_DUP_KEY = ' + CASE WHEN ignore_dup_key = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' STATISTICS_NORECOMPUTE = ' + CASE WHEN no_recompute = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' ALLOW_ROW_LOCKS = ' + CASE WHEN allow_row_locks = 1 THEN 'ON' ELSE 'OFF' END + ','

    + ' ALLOW_PAGE_LOCKS = ' + CASE WHEN allow_page_locks = 1 THEN 'ON' ELSE 'OFF' END

    ----+ ','

    ----+ ' DATA_COMPRESSION = ' + data_compression_desc

    -- + ','

    --+ ' [ ON PARTITIONS ( { <partition_number_expression> | <range> } '

    --+ ' [ , ...n ] ) ] '

    ,* FROM MyStagingData

    WHERE MyStagingData.SCHEMA_NAME <> 'sys'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 9 posts - 1 through 9 (of 9 total)

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