------
/*
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 @ColIncludedInPartitionFucntionVARCHAR(MAX)
------
--Declare Storage Variable------
DECLARE @Rowcount INT
DECLARE @Storage INT
DECLARE @IndexonFileGroupVARCHAR(MAX)
------
--Declare Misleneous variables------
DECLARE @CommaSepratorVARCHAR(1)
------
DECLARE @Object_Holder TABLE (TabIDintIDENTITY(1,1) ,
TableNamevarchar(max),
Schemanamevarchar(max)
)
INSERT INTO @Object_Holder(TableName,Schemaname)
SELECT sys.objects.NAME AS TABLENAME,SCHEMA_NAME(sys.objects.SCHEMA_ID) AS SCHEMANAME
fromsys.objects INNER JOIN sys.indexes ON
sys.objects.object_id = sys.indexes.object_id
andsys.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'
--SELECT * FROM @Object_Holder
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
--SET @TableName='Department
Print '--Index Script for Object :::::'+@TableName
CREATE Table #Index_Info_Holder (Index_RowID INT IDENTITY(1,1),
Index_Namevarchar(MAX),Index_Typevarchar(MAX),Index_IdInt,
ObjectIDINT,IsPrimaryKeyINT,IsUniqueINT,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
whereobjectid=OBJECT_ID(@SchemaName+'.'+@TableName)--@TableName)
andIndex_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='CLUSTERED' and @Is_Primary_Key=0 --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(@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)
andindex_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
andIndex_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
andPartition_ordinal=1
SELECT @Storage= Index_IncludedColPartitionOrdinal from #Index_IncludedColumnInfo
whereIndex_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.