3 thoughts on “Script Indexes SQL Server 2005 & 2008”

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

   Reply

2. Hi Alan,

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

   regards
   sufian

   Reply

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

   Reply