The following query will return following Index information from the Sql server database
1 Index Name,Created Date
2. Index definition,Index size,Index Page Size
3.How many times user seeks and scans the Index
SET NOCOUNT ON
Declare @IndexTable Table(IndexName varchar(150),IndexDef varchar(2000))
---------------------------------------------------------------
Declare @IndexDef varchar(2000)
SET @IndexDef=''
DECLARE @vNumDBs Int =0
DECLARE @vCount Int=0
--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=0
DECLARE @Is_Unique_Key INT=0
DECLARE @Data_Space_id INT=0
--Declared Variable Included Column In Index----------
DECLARE @ColName VARCHAR(max)=''
DECLARE @Index_Column_id INT=0
DECLARE @KeyOrdinalid INT=0
DECLARE @partition_ordinal INT=0
DECLARE @IsDescendingKey INT=0
DECLARE @ColIncludedInPartitionFucntion VARCHAR(MAX)=''
-------------------------------------------------------
--Declare Storage Variable-----------------------------
DECLARE @Rowcount INT=0
DECLARE @Storage INT=0
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'
--WHERE sys.objects.NAME='ServerAudit'
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'
--Select @@RowCount
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
--INSERT INTO Index_Info_Holder (Index_Name,Index_Type,Index_Id,ObjectID,IsPrimaryKey,IsUnique,data_space_id)
--1 ServerAudit_IX_AuditDate CLUSTERED 1 103723472 0 0 1
--2 ServerAudit_IX_EventType NONCLUSTERED 2 103723472 0 0 1
SELECT @vNumIndex=Isnull(Count(*),0) FROM sys.indexes where object_id=OBJECT_ID(@TableName) and type_desc!='HEAP'
--SET @vNumIndex = @@RowCount
SET @vCountIndex = 1
WHILE @vCountIndex <= @vNumIndex
BEGIN
SELECT @IndexName=name ,@IndexType= type_desc ,@Index_Id=index_id,@Is_Primary_Key=is_primary_key,@Is_Unique_Key=is_unique,@Data_Space_id=data_space_id FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY name,type_desc ,index_id,object_id,is_primary_key,is_unique,data_space_id) as RowID, 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'
) as Index_Info_Holder
where object_id=OBJECT_ID(@TableName) and RowID=@vCountIndex
If (@IndexType='CLUSTERED' and @Is_Primary_Key=1)-- OR @IndexType='NON CLUSTERED' --or
BEGIN
SET @IndexDef+= 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
SET @IndexDef+= '('
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED' or
BEGIN
SET @IndexDef+= 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] '
+ 'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE NONCLUSTERED '
SET @IndexDef+= '('
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
BEGIN
SET @IndexDef+= 'CREATE NONCLUSTERED INDEX ['+@IndexName+'] ON' + ' [' + @SchemaName + '].['
+ @TableName + ']'
SET @IndexDef+= '('
END
If @IndexType='CLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
BEGIN
SET @IndexDef+= 'CREATE CLUSTERED INDEX ['+ @IndexName +'] ON' + ' [' + @SchemaName + '].['
+ @TableName + ']'
SET @IndexDef+= '('
END
---Columns Included in Index--
DECLARE @vNumIndexIncludedCol Int =0
DECLARE @vCountIndexIncludedCol Int=0
--INSERT INTO Index_IncludedColumnInfo (Index_IncludedObjectId,Index_IncludedColName,Index_IncludedColID, Index_IncludedColKeyOrdinal,Index_IncludedColPartitionOrdinal,
--Index_IncludedColPartitionIsDescendingKey)
SET @vNumIndexIncludedCol = @@RowCount
SET @vCountIndexIncludedCol = 1
WHILE @vCountIndexIncludedCol <= @vNumIndexIncludedCol
BEGIN
SELECT @ColName=ColumnName,@Index_Column_id=index_column_id,@KeyOrdinalid=key_ordinal,@partition_ordinal=partition_ordinal,@IsDescendingKey=is_descending_key
FROM (SELECT ROW_NUMBER() OVER (ORDER BY object_id, COL_NAME(object_id(@TableName) ,column_id),index_column_id,key_ordinal,partition_ordinal) as RowID ,object_id as ObjectID,
COL_NAME(object_id(@TableName) ,column_id)as ColumnName ,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) as Index_IncludedColumnInfo
WHERE RowID=@vCountIndexIncludedCol and key_ordinal<>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
SET @IndexDef+= '['+@ColName+'] ASC' + @CommaSeprator
END
If @IsDescendingKey=1
BEGIN
SET @IndexDef+= '['+@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= partition_ordinal from
(SELECT ROW_NUMBER() OVER (ORDER BY object_id, COL_NAME(object_id(@TableName) ,column_id),index_column_id,key_ordinal,partition_ordinal) as RowID ,object_id as ObjectID,
COL_NAME(object_id(@TableName) ,column_id)as ColumnName ,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) as a
where partition_ordinal>0 SET @IndexDef+= ')'
If @IndexType='CLUSTERED' and @Is_Primary_Key=0 and @Is_Primary_Key=0
BEGIN
SET @IndexDef+= '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
SET @IndexDef+= '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
SET @IndexDef+= '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
SET @IndexDef+= '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
SET @IndexDef+= 'ON '+'['+@IndexonFileGroup+']'
--+ '(['+@ColIncludedInPartitionFucntion+'])'
END
If @ColIncludedInPartitionFucntion !=''
and @IndexonFileGroup<>'Primary'-- is not null --IS NOT NULL
BEGIN
SET @IndexDef+= 'ON '+'['+@IndexonFileGroup+']'+ '(['+@ColIncludedInPartitionFucntion+'])'
END
If @ColIncludedInPartitionFucntion !=''
and @IndexonFileGroup='Primary'-- is not null --IS NOT NULL
BEGIN
SET @IndexDef+= 'ON '+'['+@IndexonFileGroup+']'--+ '(['+@ColIncludedInPartitionFucntion+'])'
END
SET @Storage=''
--TRUNCATE TABLE Index_IncludedColumnInfo
--Print '---------End of Index Script------------------------'
SET @vCountIndex = @vCountIndex + 1
INSERT INTO @IndexTable VALUES(@IndexName,@IndexDef)
SET @IndexDef=''
END
SET @vCount = @vCount + 1
END
SELECT distinct Convert(Varchar(30),@@SERVERNAME) as Server, CONVERT(varchar(30), DB_NAME()) as DataBaseName,CONVERT(varchar, t.TABLE_SCHEMA) as TABLE_SCHEMA,
Convert(Varchar(50),Tab.[name]) AS TableName,Convert(varchar(150),Ind.[name]) AS IndexName,
CONVERT(Varchar(100), SUBSTRING((
SELECT ', ' + AC.name
FROM sys.[tables] AS T INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH('')), 2, 8000
)) AS KeyCols,
CONVERT(Varchar(100),SUBSTRING(
( SELECT ', ' + AC.name FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id] INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 1
ORDER BY IC.key_ordinal FOR XML PATH('')), 2, 8000)) AS IncludeCols
,create_date,modify_date,it.IndexDef,
f.Indexsize_KB,f.PageSize_KB,
s.USER_SEEKS, s.USER_SCANS, s.USER_LOOKUPS, s.USER_UPDATES ,
s.last_user_scan,s.last_user_seek
--INTO DBA_REP.dbo.IndexHistory
FROM sys.indexes Ind INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
INNER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME=tab.name
LEFT OUTER JOIN @IndexTable it on it.IndexName=ind.name
LEFT OUTER JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S ON s.object_id=ind.object_id AND Ind.INDEX_ID = S.INDEX_ID
LEFT OUTER JOIN
(
SELECT i.OBJECT_ID,i.index_id,8 * SUM(a.used_pages) AS 'Indexsize_KB',SUM(a.used_pages) as PageSize_KB FROM sys.indexes AS i
INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE ISNULL(i.[name] ,'')<> ''
GROUP BY i.OBJECT_ID,i.index_id,i.name
) as F ON f.index_id=ind.index_id AND ind.object_id=f.object_id
WHERE ISNULL(Ind.[name] ,'')<> '' --AND
--Ind.[type] = 2
--and Ind.is_unique = 0
--and Ind.is_primary_key = 0
--and Tab.[type] = 'U'
ORDER BY TableName
Declare @IndexTable Table(IndexName varchar(150),IndexDef varchar(2000))
---------------------------------------------------------------
Declare @IndexDef varchar(2000)
SET @IndexDef=''
DECLARE @vNumDBs Int =0
DECLARE @vCount Int=0
--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=0
DECLARE @Is_Unique_Key INT=0
DECLARE @Data_Space_id INT=0
--Declared Variable Included Column In Index----------
DECLARE @ColName VARCHAR(max)=''
DECLARE @Index_Column_id INT=0
DECLARE @KeyOrdinalid INT=0
DECLARE @partition_ordinal INT=0
DECLARE @IsDescendingKey INT=0
DECLARE @ColIncludedInPartitionFucntion VARCHAR(MAX)=''
-------------------------------------------------------
--Declare Storage Variable-----------------------------
DECLARE @Rowcount INT=0
DECLARE @Storage INT=0
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'
--WHERE sys.objects.NAME='ServerAudit'
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'
--Select @@RowCount
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
--INSERT INTO Index_Info_Holder (Index_Name,Index_Type,Index_Id,ObjectID,IsPrimaryKey,IsUnique,data_space_id)
--1 ServerAudit_IX_AuditDate CLUSTERED 1 103723472 0 0 1
--2 ServerAudit_IX_EventType NONCLUSTERED 2 103723472 0 0 1
SELECT @vNumIndex=Isnull(Count(*),0) FROM sys.indexes where object_id=OBJECT_ID(@TableName) and type_desc!='HEAP'
--SET @vNumIndex = @@RowCount
SET @vCountIndex = 1
WHILE @vCountIndex <= @vNumIndex
BEGIN
SELECT @IndexName=name ,@IndexType= type_desc ,@Index_Id=index_id,@Is_Primary_Key=is_primary_key,@Is_Unique_Key=is_unique,@Data_Space_id=data_space_id FROM
(
SELECT ROW_NUMBER() OVER (ORDER BY name,type_desc ,index_id,object_id,is_primary_key,is_unique,data_space_id) as RowID, 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'
) as Index_Info_Holder
where object_id=OBJECT_ID(@TableName) and RowID=@vCountIndex
If (@IndexType='CLUSTERED' and @Is_Primary_Key=1)-- OR @IndexType='NON CLUSTERED' --or
BEGIN
SET @IndexDef+= 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] ' +
'ADD CONSTRAINT ['+@IndexName+']' +' PRIMARY KEY CLUSTERED '
SET @IndexDef+= '('
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=1--OR @IndexType='NON CLUSTERED' or
BEGIN
SET @IndexDef+= 'ALTER TABLE ' + '[' + @SchemaName + '].[' + @TableName +'] '
+ 'ADD CONSTRAINT ['+@IndexName+']' +' UNIQUE NONCLUSTERED '
SET @IndexDef+= '('
END
If @IndexType='NONCLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
BEGIN
SET @IndexDef+= 'CREATE NONCLUSTERED INDEX ['+@IndexName+'] ON' + ' [' + @SchemaName + '].['
+ @TableName + ']'
SET @IndexDef+= '('
END
If @IndexType='CLUSTERED' and @Is_Unique_Key=0 and @Is_Primary_Key=0
BEGIN
SET @IndexDef+= 'CREATE CLUSTERED INDEX ['+ @IndexName +'] ON' + ' [' + @SchemaName + '].['
+ @TableName + ']'
SET @IndexDef+= '('
END
---Columns Included in Index--
DECLARE @vNumIndexIncludedCol Int =0
DECLARE @vCountIndexIncludedCol Int=0
--INSERT INTO Index_IncludedColumnInfo (Index_IncludedObjectId,Index_IncludedColName,Index_IncludedColID, Index_IncludedColKeyOrdinal,Index_IncludedColPartitionOrdinal,
--Index_IncludedColPartitionIsDescendingKey)
SET @vNumIndexIncludedCol = @@RowCount
SET @vCountIndexIncludedCol = 1
WHILE @vCountIndexIncludedCol <= @vNumIndexIncludedCol
BEGIN
SELECT @ColName=ColumnName,@Index_Column_id=index_column_id,@KeyOrdinalid=key_ordinal,@partition_ordinal=partition_ordinal,@IsDescendingKey=is_descending_key
FROM (SELECT ROW_NUMBER() OVER (ORDER BY object_id, COL_NAME(object_id(@TableName) ,column_id),index_column_id,key_ordinal,partition_ordinal) as RowID ,object_id as ObjectID,
COL_NAME(object_id(@TableName) ,column_id)as ColumnName ,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) as Index_IncludedColumnInfo
WHERE RowID=@vCountIndexIncludedCol and key_ordinal<>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
SET @IndexDef+= '['+@ColName+'] ASC' + @CommaSeprator
END
If @IsDescendingKey=1
BEGIN
SET @IndexDef+= '['+@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= partition_ordinal from
(SELECT ROW_NUMBER() OVER (ORDER BY object_id, COL_NAME(object_id(@TableName) ,column_id),index_column_id,key_ordinal,partition_ordinal) as RowID ,object_id as ObjectID,
COL_NAME(object_id(@TableName) ,column_id)as ColumnName ,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) as a
where partition_ordinal>0 SET @IndexDef+= ')'
If @IndexType='CLUSTERED' and @Is_Primary_Key=0 and @Is_Primary_Key=0
BEGIN
SET @IndexDef+= '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
SET @IndexDef+= '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
SET @IndexDef+= '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
SET @IndexDef+= '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
SET @IndexDef+= 'ON '+'['+@IndexonFileGroup+']'
--+ '(['+@ColIncludedInPartitionFucntion+'])'
END
If @ColIncludedInPartitionFucntion !=''
and @IndexonFileGroup<>'Primary'-- is not null --IS NOT NULL
BEGIN
SET @IndexDef+= 'ON '+'['+@IndexonFileGroup+']'+ '(['+@ColIncludedInPartitionFucntion+'])'
END
If @ColIncludedInPartitionFucntion !=''
and @IndexonFileGroup='Primary'-- is not null --IS NOT NULL
BEGIN
SET @IndexDef+= 'ON '+'['+@IndexonFileGroup+']'--+ '(['+@ColIncludedInPartitionFucntion+'])'
END
SET @Storage=''
--TRUNCATE TABLE Index_IncludedColumnInfo
--Print '---------End of Index Script------------------------'
SET @vCountIndex = @vCountIndex + 1
INSERT INTO @IndexTable VALUES(@IndexName,@IndexDef)
SET @IndexDef=''
END
SET @vCount = @vCount + 1
END
SELECT distinct Convert(Varchar(30),@@SERVERNAME) as Server, CONVERT(varchar(30), DB_NAME()) as DataBaseName,CONVERT(varchar, t.TABLE_SCHEMA) as TABLE_SCHEMA,
Convert(Varchar(50),Tab.[name]) AS TableName,Convert(varchar(150),Ind.[name]) AS IndexName,
CONVERT(Varchar(100), SUBSTRING((
SELECT ', ' + AC.name
FROM sys.[tables] AS T INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id]
INNER JOIN sys.[index_columns] IC ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id]
AND Ind.index_id = I.index_id AND IC.is_included_column = 0
ORDER BY IC.key_ordinal
FOR XML PATH('')), 2, 8000
)) AS KeyCols,
CONVERT(Varchar(100),SUBSTRING(
( SELECT ', ' + AC.name FROM sys.[tables] AS T
INNER JOIN sys.[indexes] I ON T.[object_id] = I.[object_id] INNER JOIN sys.[index_columns] IC
ON I.[object_id] = IC.[object_id] AND I.[index_id] = IC.[index_id]
INNER JOIN sys.[all_columns] AC ON T.[object_id] = AC.[object_id] AND IC.[column_id] = AC.[column_id]
WHERE Ind.[object_id] = I.[object_id] AND Ind.index_id = I.index_id AND IC.is_included_column = 1
ORDER BY IC.key_ordinal FOR XML PATH('')), 2, 8000)) AS IncludeCols
,create_date,modify_date,it.IndexDef,
f.Indexsize_KB,f.PageSize_KB,
s.USER_SEEKS, s.USER_SCANS, s.USER_LOOKUPS, s.USER_UPDATES ,
s.last_user_scan,s.last_user_seek
--INTO DBA_REP.dbo.IndexHistory
FROM sys.indexes Ind INNER JOIN sys.[tables] AS Tab ON Tab.[object_id] = Ind.[object_id]
INNER JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_NAME=tab.name
LEFT OUTER JOIN @IndexTable it on it.IndexName=ind.name
LEFT OUTER JOIN SYS.DM_DB_INDEX_USAGE_STATS AS S ON s.object_id=ind.object_id AND Ind.INDEX_ID = S.INDEX_ID
LEFT OUTER JOIN
(
SELECT i.OBJECT_ID,i.index_id,8 * SUM(a.used_pages) AS 'Indexsize_KB',SUM(a.used_pages) as PageSize_KB FROM sys.indexes AS i
INNER JOIN sys.partitions AS p ON p.OBJECT_ID = i.OBJECT_ID AND p.index_id = i.index_id
INNER JOIN sys.allocation_units AS a ON a.container_id = p.partition_id
WHERE ISNULL(i.[name] ,'')<> ''
GROUP BY i.OBJECT_ID,i.index_id,i.name
) as F ON f.index_id=ind.index_id AND ind.object_id=f.object_id
WHERE ISNULL(Ind.[name] ,'')<> '' --AND
--Ind.[type] = 2
--and Ind.is_unique = 0
--and Ind.is_primary_key = 0
--and Tab.[type] = 'U'
ORDER BY TableName
No comments:
Post a Comment