Home > Generate Index Script SQL Server 2005 & 2008, SQLServer2008 > Script Indexes SQL Server 2005 & 2008

Script Indexes SQL Server 2005 & 2008

Download Updated Script : Index_Script
This script will help DBA's in scripting out all the existing
indexes(Clustered , Non-Clustered, Clustered with PK,
Clustered with Unique key, Non-Clustered with Unique Key etc.)
in a Database.Script will script the Partition Indexes which
exists on Partition Scheme,Primary Filegroup or on any Filegroup
In SQL Server 2008 there is no way to script a index with
Filegroup or Partition Scheme Name.
It seems to me as a BUG
as In SQL Server 2005 we can script the Index with Filegroup
and Partition Scheme Name.
Hope it will help lot of DBA's.
--------------------------------------------------------------
/*
Code Developed By      : Mohd Sufian
Code Developed Date    : 14th Novermber 2009
Code Developed Country : India
*/
SET NOCOUNT ON
---------------------------------------------------------------
      DECLARE @vNumDBs    Int 
      DECLARE @vCount     Int
--Decalared Variable Index Info-----------------------
      DECLARE @SchemaName           Varchar(MAX)
      DECLARE @TableName            Varchar(MAX)
      DECLARE @IndexName            Varchar(MAX)
      DECLARE @IndexType            Varchar(MAX)
      DECLARE @Index_Id             Varchar(MAX)
      DECLARE @Is_Primary_Key       INT
      DECLARE @Is_Unique_Key        INT
      DECLARE @Data_Space_id        INT
--Declared Variable Included Column In Index----------
      DECLARE @ColName              VARCHAR(max)
      DECLARE @Index_Column_id      INT
      DECLARE @KeyOrdinalid         INT
      DECLARE @partition_ordinal    INT
      DECLARE @IsDescendingKey      INT
      DECLARE @ColIncludedInPartitionFucntion VARCHAR(MAX)
-------------------------------------------------------
--Declare Storage Variable-----------------------------
      DECLARE @Rowcount INT
      DECLARE @Storage  INT
      DECLARE @IndexonFileGroup VARCHAR(MAX)
-------------------------------------------------------
--Declare Misleneous variables-------------------------
      DECLARE @CommaSeprator VARCHAR(1)
-------------------------------------------------------
DECLARE @Object_Holder TABLE (TabID int IDENTITY(1,1) ,
TableName varchar(max),Schemaname varchar(max))
INSERT INTO @Object_Holder(TableName,Schemaname)
SELECT sys.objects.NAME AS TABLENAME,
SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
from sys.objects
INNER JOIN   sys.indexes ON
sys.objects.object_id = sys.indexes.object_id
and sys.indexes.type_desc!='HEAP'
GROUP BY sys.objects.name,
SCHEMA_NAME(sys.objects.SCHEMA_ID),sys.objects.type
HAVING (sys.objects.type='U') and sys.objects.name<>'sysdiagrams'
order by sys.objects.name --and sys.objects.name='Test1'
SET @vNumDBs = @@RowCount 
SET @vCount = 1
While @vCount <= @vNumDBs 
BEGIN
SELECT @SchemaName=Schemaname,@TableName=TableName
FROM @Object_Holder where TabID=@vCount
      ---Check for Indexes on Each Objects
      DECLARE @vNumIndex            Int 
      DECLARE @vCountIndex          Int

      Print '--Index Script for Object :::::'+@TableName
      CREATE Table #Index_Info_Holder (Index_RowID INT IDENTITY(1,1),
      Index_Name varchar(MAX),Index_Type varchar(MAX),Index_Id Int,
      ObjectID INT,IsPrimaryKey INT,IsUnique INT,data_space_id INT)
      INSERT INTO #Index_Info_Holder (Index_Name,Index_Type,Index_Id,
      ObjectID,IsPrimaryKey,IsUnique,data_space_id)
      SELECT name , type_desc ,index_id,object_id,is_primary_key,
      is_unique,data_space_id FROM sys.indexes where
      object_id=OBJECT_ID(@TableName) and type_desc!='HEAP'
      --'CDS_BreakMaster')--(@TableName)
      SET @vNumIndex = @@RowCount 
      SET @vCountIndex = 1
            WHILE  @vCountIndex <= @vNumIndex
      BEGIN
      SELECT @IndexName=Index_name ,@IndexType= Index_type ,
      @Index_Id=index_id,@Is_Primary_Key=IsPrimaryKey,
      @Is_Unique_Key=IsUnique,@Data_Space_id=data_space_id
      FROM #Index_Info_Holder
      where objectid=OBJECT_ID(@TableName)
      and Index_RowID=@vCountIndex
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=1 --OR
      @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
                         'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
      Print '('
      END
      If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] '
                           + 'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE NONCLUSTERED '
      Print '('
      END
      If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
      BEGIN
      Print 'CREATE NONCLUSTERED INDEX ['+@IndexName+'] ON' + ' [' + @SchemaName + '].['
                                         + @TableName  + ']'
      Print '('
      END
      If @IndexType='CLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
      BEGIN
      Print 'CREATE CLUSTERED INDEX ['+ @IndexName +'] ON' + ' [' + @SchemaName + '].['
                                      + @TableName + ']'
      Print '('
      END
      ---Columns Included in Index--
      DECLARE @vNumIndexIncludedCol       Int 
      DECLARE @vCountIndexIncludedCol     Int
      CREATE TABLE  #Index_IncludedColumnInfo
      (
      Index_IncludedColumnRowID                   INT IDENTITY(1,1),
      Index_IncludedObjectId                      INT,
      Index_IncludedColName                       Varchar(MAX),
      Index_IncludedColID                         INT,
      Index_IncludedColKeyOrdinal                 INT,
      Index_IncludedColPartitionOrdinal           INT,
      Index_IncludedColPartitionIsDescendingKey   INT
      )   
      INSERT INTO #Index_IncludedColumnInfo
      (Index_IncludedObjectId,Index_IncludedColName,Index_IncludedColID,
       Index_IncludedColKeyOrdinal,Index_IncludedColPartitionOrdinal,
       Index_IncludedColPartitionIsDescendingKey)SELECT object_id,
       COL_NAME(object_id(@TableName),column_id),index_column_id,
       key_ordinal,partition_ordinal,is_descending_key FROM
       sys.index_columns where Object_Id=object_id(@TableName)|
      and index_id=@Index_Id and key_ordinal<>0--and Partition_ordinal!=1  
      SET @vNumIndexIncludedCol = @@RowCount 
      SET @vCountIndexIncludedCol = 1
      WHILE  @vCountIndexIncludedCol  <= @vNumIndexIncludedCol
      BEGIN
      SELECT @ColName=Index_IncludedColName,@Index_Column_id=Index_IncludedColID,
      @KeyOrdinalid=Index_IncludedColKeyOrdinal,
      @partition_ordinal=Index_IncludedColPartitionOrdinal,
      @IsDescendingKey=Index_IncludedColPartitionIsDescendingKey

      FROM #Index_IncludedColumnInfo WHERE
      Index_IncludedColumnRowID=@vCountIndexIncludedCol
      and Index_IncludedColKeyOrdinal<>0
            If @vCountIndexIncludedCol=@vNumIndexIncludedCol
            --or @vCountIndexIncludedCol  != @vNumIndexIncludedCol
                  BEGIN
                  SELECT @CommaSeprator=' '
            END
            If @vCountIndexIncludedCol<>@vNumIndexIncludedCol
            --and @vCountIndexIncludedCol  != @vCountIndexIncludedCol
                  BEGIN
                        SELECT @CommaSeprator=','
                  END 
      If @IsDescendingKey=0
            BEGIN
            Print '['+@ColName+'] ASC' + @CommaSeprator
            END
      If @IsDescendingKey=1
            BEGIN
            Print '['+@ColName+'] DESC'
            END
      SET @ColName=''
      SET @vCountIndexIncludedCol = @vCountIndexIncludedCol + 1
      END
      SELECT @ColIncludedInPartitionFucntion=COL_NAME(object_id(@TableName),column_id)
      FROM sys.index_columns where Object_Id=object_id(@TableName) and index_id=@Index_Id
      and Partition_ordinal=1   
      SELECT @Storage= Index_IncludedColPartitionOrdinal from #Index_IncludedColumnInfo
      where Index_IncludedColPartitionOrdinal>0
      Print ')'
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=0 and  @Is_Primary_Key=0
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
      If @IndexType='CLUSTERED' and  @Is_Primary_Key=1
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
    If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED'  or
      BEGIN
      Print 'WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
          IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
          ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80)'
    END
    If @IndexType='NONCLUSTERED' and  @Is_Unique_Key=0 and @Is_Primary_Key=0
    BEGIN
    PRINT 'WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = ON, SORT_IN_TEMPDB = OFF,
               IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
               ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 70)'
      END
      SELECT @IndexonFileGroup=[name] FROM SYS.DATA_SPACES WHERE data_space_id=@Data_Space_id
    If @ColIncludedInPartitionFucntion IS NULL
         BEGIN
         SET @ColIncludedInPartitionFucntion=' '
         END
       If @ColIncludedInPartitionFucntion =''--IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'
        --+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
       If @ColIncludedInPartitionFucntion !=''
          and @IndexonFileGroup<>'Primary'-- is not null --IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
       If @ColIncludedInPartitionFucntion !=''
          and @IndexonFileGroup='Primary'-- is not null --IS NOT NULL
       BEGIN
       PRINT  'ON '+'['+@IndexonFileGroup+']'--+ '(['+@ColIncludedInPartitionFucntion+'])'
       END
      SET @Storage=''
      DROP TABLE #Index_IncludedColumnInfo
      Print '---------End of Index Script------------------------'
      SET @vCountIndex = @vCountIndex + 1     
      END
      DROP TABLE #Index_Info_Holder
      --**********************************--
 SET @vCount = @vCount + 1 
END
SET NOCOUNT OFF
Note : The script will  Generate the create script for Constraints and Indexes.  
  1. August 24, 2010 at 6:33 am

    Thank you so much for this script. I’ve been looking for one like this for almost an entire week!
    Keep it up!

  2. John
    July 28, 2011 at 1:57 pm

    will this script drop/recreate if the index exists or will it throw an error?

  3. Alan
    October 31, 2011 at 3:27 pm

    The code is cut off. Is there someplace to download it?

    • msufian
      October 31, 2011 at 4:17 pm

      Hi Alan,

      I have sent the code as attachment in a separate mail.

      regards
      sufian

  4. Julian
    May 29, 2012 at 1:17 pm

    I found this scripts useful thanks. I modified it so you ::
    – Wrap index creation in IF NOT EXISTS statements (and control the indent)
    – Only scripts indices / constraints for a the given schema, tableName or both

    The code below:
    ————————————————————–
    /* This script will Generate the create script for Constraints
    * and Indexes.
    */
    ————————————————————–
    /*
    Code Developed By : Mohd Sufian
    Code Developed Date : 14th Novermber 2009
    Code Developed Country : India
    Extended / formatted by: Julian Barrable – 28th May 2012
    */
    —————————————————————
    — Extended —
    DECLARE @WrapInIfExists INT = 1
    DECLARE @LimitForTable VARCHAR(500) = ”
    DECLARE @LimitForSchema VARCHAR(500) = ”
    DECLARE @Spacer VARCHAR(10) = ”

    SET @WrapInIfExists = 1
    SET @LimitForTable = ”
    SET @LimitForSchema = ”

    IF @WrapInIfExists = 1
    SET @Spacer = ‘ ‘
    —————————————————————
    SET NOCOUNT ON
    DECLARE @vNumDBs Int
    DECLARE @vCount Int
    –Decalared Variable Index Info———————–
    DECLARE @SchemaName Varchar(MAX)
    DECLARE @TableName Varchar(MAX)
    DECLARE @IndexName Varchar(MAX)
    DECLARE @IndexType Varchar(MAX)
    DECLARE @Index_Id Varchar(MAX)
    DECLARE @Is_Primary_Key INT
    DECLARE @Is_Unique_Key INT
    DECLARE @Data_Space_id INT
    –Declared Variable Included Column In Index———-
    DECLARE @ColName VARCHAR(max)
    DECLARE @Index_Column_id INT
    DECLARE @KeyOrdinalid INT
    DECLARE @partition_ordinal INT
    DECLARE @IsDescendingKey INT
    DECLARE @ColIncludedInPartitionFucntion VARCHAR(MAX)
    ——————————————————-
    –Declare Storage Variable—————————–
    DECLARE @Rowcount INT
    DECLARE @Storage INT
    DECLARE @IndexonFileGroup VARCHAR(MAX)
    ——————————————————-
    –Declare Misleneous variables————————-
    DECLARE @CommaSeprator VARCHAR(1)
    ——————————————————-
    CREATE TABLE #Object_Holder_Temp (
    TableName varchar(max),
    Schemaname varchar(max)
    )
    INSERT INTO #Object_Holder_Temp(TableName,Schemaname)
    SELECT
    sys.objects.NAME AS TABLENAME,
    SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
    FROM sys.objects
    INNER JOIN sys.indexes ON sys.objects.object_id = sys.indexes.object_id AND sys.indexes.type_desc != ‘HEAP’
    GROUP BY
    sys.objects.name,
    SCHEMA_NAME(sys.objects.SCHEMA_ID),
    sys.objects.type
    HAVING (sys.objects.type=’U’) AND sys.objects.name ‘sysdiagrams’ — AND sys.objects.name = ‘BillOfMaterials’
    ORDER BY sys.objects.name — AND sys.objects.name = ‘Test1

    — Remove any unwanted schema objects
    IF @LimitForSchema ”
    DELETE FROM #Object_Holder_Temp WHERE Schemaname @LimitForSchema

    — Remove any unwanted tables
    IF @LimitForTable ”
    DELETE FROM #Object_Holder_Temp WHERE TableName @LimitForTable

    DECLARE @Object_Holder TABLE (
    TabID int IDENTITY(1,1),
    TableName varchar(max),
    Schemaname varchar(max)
    )
    INSERT INTO @Object_Holder(TableName,Schemaname)
    SELECT * FROM #Object_Holder_Temp

    DROP TABLE #Object_Holder_Temp

    SET @vNumDBs = (SELECT COUNT(*) FROM @Object_Holder)
    SET @vCount = 1

    WHILE @vCount <= @vNumDBs
    BEGIN
    SELECT @SchemaName = Schemaname,
    @TableName = TableName
    FROM @Object_Holder
    WHERE TabID = @vCount

    —Check for Indexes on Each Objects
    DECLARE @vNumIndex INT
    DECLARE @vCountIndex INT
    –SET @TableName='Department

    Print '——————————————————————–'
    Print '– All Indexes for object [' + @SchemaName + '].[' + @TableName + ']'
    Print '——————————————————————–'
    Print ''

    CREATE TABLE #Index_Info_Holder (
    Index_RowID INT IDENTITY(1,1),
    Index_Name varchar(MAX),
    Index_Type varchar(MAX),
    Index_Id INT,
    ObjectID INT,
    IsPrimaryKey INT,
    IsUnique INT,
    data_space_id INT
    )

    INSERT INTO #Index_Info_Holder (Index_Name, Index_Type, Index_Id, ObjectID, IsPrimaryKey, IsUnique, data_space_id)
    SELECT name,
    type_desc,
    index_id,
    object_id,
    is_primary_key,
    is_unique,
    data_space_id
    FROM sys.indexes
    WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
    AND type_desc != 'HEAP'

    SET @vNumIndex = @@RowCount
    SET @vCountIndex = 1
    WHILE @vCountIndex <= @vNumIndex
    BEGIN
    SELECT @IndexName = Index_name,
    @IndexType = Index_type,
    @Index_Id = index_id,
    @Is_Primary_Key = IsPrimaryKey,
    @Is_Unique_Key = IsUnique,
    @Data_Space_id = data_space_id
    FROM #Index_Info_Holder
    WHERE objectid = OBJECT_ID(@SchemaName+'.'+@TableName)
    AND Index_RowID = @vCountIndex

    IF @WrapInIfExists = 1
    BEGIN
    Print 'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''['+@SchemaName+'].['+@TableName+']'') AND name = N'''+@IndexName+''')'
    Print 'BEGIN'
    Print @Spacer + 'Print ''Creating new INDEX '+@IndexName+' ON ['+@SchemaName+'].['+@TableName+']'''
    END

    IF @IndexType='CLUSTERED' and @Is_Primary_Key=1 –OR @IndexType='NON CLUSTERED' or
    BEGIN
    Print @Spacer + 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
    'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
    Print @Spacer + '('
    END
    IF @IndexType='CLUSTERED' and @Is_Primary_Key=0 –OR @IndexType='NON CLUSTERED' or
    BEGIN
    Print @Spacer + 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
    'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
    Print @Spacer + '('
    END
    IF @IndexType='NONCLUSTERED' and @Is_Unique_Key=1–OR @IndexType='NON CLUSTERED' or
    BEGIN
    Print @Spacer + 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] '
    + 'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE NONCLUSTERED '
    Print @Spacer + '('
    END
    IF @IndexType='NONCLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
    BEGIN
    Print @Spacer + 'CREATE NONCLUSTERED INDEX ['+@IndexName+'] ON' + ' [' + @SchemaName + '].['
    + @TableName + ']'
    Print @Spacer + '('
    END
    IF @IndexType='CLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
    BEGIN
    Print @Spacer + 'CREATE CLUSTERED INDEX ['+ @IndexName +'] ON' + ' [' + @SchemaName + '].['
    + @TableName + ']'
    Print @Spacer + '('
    END

    —Columns Included in Index–
    DECLARE @vNumIndexIncludedCol INT
    DECLARE @vCountIndexIncludedCol INT
    CREATE TABLE #Index_IncludedColumnInfo
    (
    Index_IncludedColumnRowID INT IDENTITY(1,1),
    Index_IncludedObjectId INT,
    Index_IncludedColName Varchar(MAX),
    Index_IncludedColID INT,
    Index_IncludedColKeyOrdinal INT,
    Index_IncludedColPartitionOrdinal INT,
    Index_IncludedColPartitionIsDescendingKey INT
    )

    INSERT INTO #Index_IncludedColumnInfo
    (
    Index_IncludedObjectId,Index_IncludedColName,Index_IncludedColID,
    Index_IncludedColKeyOrdinal,Index_IncludedColPartitionOrdinal,
    Index_IncludedColPartitionIsDescendingKey
    )
    SELECT object_id,
    COL_NAME(object_id(@SchemaName + '.' + @TableName), column_id),
    index_column_id,
    key_ordinal,
    partition_ordinal,
    is_descending_key
    FROM sys.index_columns
    WHERE Object_Id = object_id(@SchemaName + '.' + @TableName)
    AND index_id=@Index_Id
    AND key_ordinal 0

    SET @vNumIndexIncludedCol = @@RowCount
    SET @vCountIndexIncludedCol = 1

    WHILE @vCountIndexIncludedCol <= @vNumIndexIncludedCol
    BEGIN
    SELECT @ColName = Index_IncludedColName,
    @Index_Column_id = Index_IncludedColID,
    @KeyOrdinalid = Index_IncludedColKeyOrdinal,
    @partition_ordinal = Index_IncludedColPartitionOrdinal,
    @IsDescendingKey = Index_IncludedColPartitionIsDescendingKey
    FROM #Index_IncludedColumnInfo
    WHERE Index_IncludedColumnRowID = @vCountIndexIncludedCol
    AND Index_IncludedColKeyOrdinal 0

    IF @vCountIndexIncludedCol = @vNumIndexIncludedCol
    BEGIN
    SELECT @CommaSeprator = ‘ ‘
    END

    IF @vCountIndexIncludedCol @vNumIndexIncludedCol
    BEGIN
    SELECT @CommaSeprator = ‘,’
    END

    If @IsDescendingKey=0
    BEGIN
    Print @Spacer + @Spacer + ‘[‘+@ColName+’] ASC’ + @CommaSeprator
    END
    If @IsDescendingKey=1
    BEGIN
    Print @Spacer + @Spacer + ‘[‘+@ColName+’] DESC’
    END

    SET @ColName=”
    SET @vCountIndexIncludedCol = @vCountIndexIncludedCol + 1
    END

    SELECT @ColIncludedInPartitionFucntion = COL_NAME(object_id(@TableName),column_id)
    FROM sys.index_columns
    WHERE Object_Id=object_id(@TableName)
    AND index_id = @Index_Id
    AND Partition_ordinal = 1

    SELECT @Storage = Index_IncludedColPartitionOrdinal
    FROM #Index_IncludedColumnInfo
    WHERE Index_IncludedColPartitionOrdinal > 0

    Print @Spacer + ‘)’

    IF @IndexType=’CLUSTERED’ AND @Is_Primary_Key=0 AND @Is_Primary_Key = 0
    BEGIN
    Print @Spacer + ‘WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)’
    END
    IF @IndexType = ‘CLUSTERED’ AND @Is_Primary_Key = 1
    BEGIN
    Print @Spacer + ‘WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)’
    END
    IF @IndexType = ‘NONCLUSTERED’ AND @Is_Unique_Key = 1 –OR @IndexType=’NON CLUSTERED’ or
    BEGIN
    Print @Spacer + ‘WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)’
    END
    IF @IndexType = ‘NONCLUSTERED’ AND @Is_Unique_Key = 0 AND @Is_Primary_Key = 0
    BEGIN
    PRINT @Spacer + ‘WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 70)’
    END

    SELECT @IndexonFileGroup = [name]
    FROM SYS.DATA_SPACES
    WHERE data_space_id = @Data_Space_id

    IF @ColIncludedInPartitionFucntion IS NULL
    BEGIN
    SET @ColIncludedInPartitionFucntion=’ ‘
    END
    IF @ColIncludedInPartitionFucntion = ” — IS NOT NULL
    BEGIN
    PRINT @Spacer + ‘ON ‘+'[‘+@IndexonFileGroup+’]’
    END

    IF @ColIncludedInPartitionFucntion != ”AND @IndexonFileGroup’Primary’ — IS NOT NULL
    BEGIN
    PRINT @Spacer + ‘ON ‘+'[‘+@IndexonFileGroup+’]’+ ‘([‘+@ColIncludedInPartitionFucntion+’])’
    END

    IF @ColIncludedInPartitionFucntion != ” AND @IndexonFileGroup = ‘Primary’ — IS NOT NULL
    BEGIN
    PRINT @Spacer + ‘ON ‘+'[‘+@IndexonFileGroup+’]’–+ ‘([‘+@ColIncludedInPartitionFucntion+’])’
    END

    SET @Storage=”
    DROP TABLE #Index_IncludedColumnInfo

    Print ‘END’
    Print ”
    Print ‘GO’
    Print ‘——— End of Create Index Block ———‘

    SET @vCountIndex = @vCountIndex + 1

    END
    DROP TABLE #Index_Info_Holder
    –**********************************–
    SET @vCount = @vCount + 1
    END
    SET NOCOUNT OFF

  5. Deepak
    September 8, 2012 at 10:02 am

    thanks, this saved me, and alot of people alot of time and effort…

  6. June 24, 2013 at 11:30 pm

    Thank You.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: