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.
will this script drop/recreate if the index exists or will it throw an error?
Hi Alan,
I have sent the code as attachment in a separate mail.
regards
sufian
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