Problem: Sometime DBA’s want to get script of indexes from their current database. How anyone can get “Drop and Create script” directly from any database is the another problem!!!
Solution: We have different two below scripts from which we could get “Drop and Create script”, both scripts are working identically though I urge to test both of the scripts first in your test environment and then pick for your production environment.
Generate CREATE Index script for all indexes Script-1:
--------------------------------------------------------------
--------Developer: Technothirsty.com -------------------------
--------Date: 16-06-2017---------------------------------
--------Purpose: Get all existing indexes,------------------
-------- but NOT the primary and unique keys--------
--------------------------------------------------------------
--------------------------------------------------------------
DECLARE
@IncludeFileGroup bit = 1,
@IncludeDrop bit = 1,
@IncludeFillFactor bit = 1
-- Get all existing indexes, but NOT the primary and unique keys
DECLARE Indexes_cursor CURSOR
FOR SELECT
SC.Name AS SchemaName
, SO.Name AS TableName
, SI.Object_Id AS TableId
, SI.[Name] AS IndexName
, SI.Index_ID AS IndexId
, FG.[Name] AS FileGroupName
, CASE WHEN SI.Fill_Factor = 0 THEN 100 ELSE SI.Fill_Factor END Fill_Factor
FROM sys.indexes SI
LEFT JOIN sys.filegroups FG
ON SI.data_space_id = FG.data_space_id
INNER JOIN sys.objects SO
ON SI.object_id = SO.object_id
INNER JOIN sys.schemas SC
ON SC.schema_id = SO.schema_id
WHERE ObjectProperty(SI.Object_Id, 'IsUserTable') = 1
AND SI.[Name] IS NOT NULL
AND SI.is_primary_key = 0
AND SI.is_unique_constraint = 0
AND IndexProperty(SI.Object_Id, SI.[Name], 'IsStatistics') = 0
ORDER BY Object_name(SI.Object_Id), SI.Index_ID
DECLARE @SchemaName sysname
DECLARE @TableName sysname
DECLARE @TableId int
DECLARE @IndexName sysname
DECLARE @FileGroupName sysname
DECLARE @IndexId int
DECLARE @FillFactor int
DECLARE @NewLine nvarchar(4000) SET @NewLine = CHAR(13) + CHAR(10)
DECLARE @Tab nvarchar(4000) SET @Tab = Space(4)
-- Loop through all indexes
OPEN Indexes_cursor
FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
WHILE (@@Fetch_Status = 0)
BEGIN
DECLARE @sIndexDesc nvarchar(4000)
DECLARE @sCreateSql nvarchar(4000)
DECLARE @sDropSql nvarchar(4000)
SET @sIndexDesc = '-- Index ' + @IndexName + ' on table ' + @TableName
SET @sDropSql = 'IF NOT EXISTS(SELECT 1' + @NewLine
+ ' FROM sysindexes si' + @NewLine
+ ' INNER JOIN sysobjects so' + @NewLine
+ ' ON so.id = si.id' + @NewLine
+ ' WHERE si.[Name] = N''' + @IndexName + ''' -- Index Name' + @NewLine
+ ' AND so.[Name] = N''' + @TableName + ''') -- Table Name' + @NewLine
+ 'BEGIN' + @NewLine
--+ ' DROP INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine
SET @sCreateSql = 'CREATE '
-- Check if the index is unique
IF (IndexProperty(@TableId, @IndexName, 'IsUnique') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'UNIQUE '
END
--END IF
-- Check if the index is clustered
IF (IndexProperty(@TableId, @IndexName, 'IsClustered') = 1)
BEGIN
SET @sCreateSql = @sCreateSql + 'CLUSTERED '
END
--END IF
SET @sCreateSql = @sCreateSql + 'INDEX [' + @IndexName + '] ON [' + @SchemaName + '].[' + @TableName + ']' + @NewLine + '(' + @NewLine
-- Get all columns of the index
DECLARE IndexColumns_cursor CURSOR
FOR SELECT SC.[Name],
IC.[is_included_column],
IC.is_descending_key
FROM sys.index_columns IC
INNER JOIN sys.columns SC
ON IC.Object_Id = SC.Object_Id
AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_Id = @TableId
AND Index_ID = @IndexId
ORDER BY IC.[is_included_column],IC.key_ordinal
DECLARE @IxColumn sysname
DECLARE @IxIncl bit
DECLARE @Desc bit
DECLARE @IxIsIncl bit SET @IxIsIncl = 0
DECLARE @IxFirstColumn bit SET @IxFirstColumn = 1
-- Loop through all columns of the index and append them to the CREATE statement
OPEN IndexColumns_cursor
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
WHILE (@@Fetch_Status = 0)
BEGIN
IF (@IxFirstColumn = 1)
BEGIN
SET @IxFirstColumn = 0
END
ELSE
BEGIN
--check to see if it's an included column
IF (@IxIsIncl = 0) AND (@IxIncl = 1)
BEGIN
SET @IxIsIncl = 1
SET @sCreateSql = @sCreateSql + @NewLine + ')' + @NewLine + 'INCLUDE' + @NewLine + '(' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ',' + @NewLine
END
--END IF
END
--END IF
SET @sCreateSql = @sCreateSql + @Tab + '[' + @IxColumn + ']'
-- check if ASC or DESC
IF @IxIsIncl = 0
BEGIN
IF @Desc = 1
BEGIN
SET @sCreateSql = @sCreateSql + ' DESC'
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + ' ASC'
END
--END IF
END
--END IF
FETCH NEXT
FROM IndexColumns_cursor
INTO @IxColumn, @IxIncl, @Desc
END
--END WHILE
CLOSE IndexColumns_cursor
DEALLOCATE IndexColumns_cursor
SET @sCreateSql = @sCreateSql + @NewLine + ') '
IF @IncludeFillFactor = 1
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine + 'WITH (FillFactor = ' + Cast(@FillFactor as varchar(13)) + ')' + @NewLine
END
--END IF
IF @IncludeFileGroup = 1
BEGIN
SET @sCreateSql = @sCreateSql + 'ON ['+ @FileGroupName + ']' + @NewLine
END
ELSE
BEGIN
SET @sCreateSql = @sCreateSql + @NewLine
END
--END IF
PRINT '-- **********************************************************************'
PRINT @sIndexDesc
PRINT '-- **********************************************************************'
IF @IncludeDrop = 1
BEGIN
PRINT @sDropSql
--PRINT 'GO'
END
--END IF
PRINT @sCreateSql
PRINT 'PRINT ''EXECUTED: '+ @sIndexDesc+''''
PRINT 'END' + @NewLine
PRINT 'GO' + @NewLine + @NewLine
FETCH NEXT
FROM Indexes_cursor
INTO @SchemaName, @TableName, @TableId, @IndexName, @IndexId, @FileGroupName, @FillFactor
END
--END WHILE
CLOSE Indexes_cursor
DEALLOCATE Indexes_cursor
Generate CREATE Index script for all indexes Script-2:
declare @SchemaName varchar(100)
declare @TableName varchar(256)
declare @IndexName varchar(256)
declare @ColumnName varchar(100)
declare @is_unique varchar(100)
declare @IndexTypeDesc varchar(100)
declare @FileGroupName varchar(100)
declare @is_disabled varchar(100)
declare @IndexOptions varchar(max)
declare @IndexColumnId int
declare @IsDescendingKey int
declare @IsIncludedColumn int
declare @TSQLScripCreationIndex varchar(max)
declare @TSQLScripDisableIndex varchar(max)
declare CursorIndex cursor for
select schema_name(t.schema_id) [schema_name], t.name, ix.name,
case when ix.is_unique = 1 then 'UNIQUE ' else '' END
, ix.type_desc,
case when ix.is_padded=1 then 'PAD_INDEX = ON, ' else 'PAD_INDEX = OFF, ' end
+ case when ix.allow_page_locks=1 then 'ALLOW_PAGE_LOCKS = ON, ' else 'ALLOW_PAGE_LOCKS = OFF, ' end
+ case when ix.allow_row_locks=1 then 'ALLOW_ROW_LOCKS = ON, ' else 'ALLOW_ROW_LOCKS = OFF, ' end
+ case when INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 then 'STATISTICS_NORECOMPUTE = ON, ' else 'STATISTICS_NORECOMPUTE = OFF, ' end
+ case when ix.ignore_dup_key=1 then 'IGNORE_DUP_KEY = ON, ' else 'IGNORE_DUP_KEY = OFF, ' end
+ 'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) AS IndexOptions
, ix.is_disabled , FILEGROUP_NAME(ix.data_space_id) FileGroupName
from sys.tables t
inner join sys.indexes ix on t.object_id=ix.object_id
where ix.type>0 and ix.is_primary_key=0 and ix.is_unique_constraint=0 --and schema_name(tb.schema_id)= @SchemaName and tb.name=@TableName
and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
order by schema_name(t.schema_id), t.name, ix.name
open CursorIndex
fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
while (@@fetch_status=0)
begin
declare @IndexColumns varchar(max)
declare @IncludedColumns varchar(max)
set @IndexColumns=''
set @IncludedColumns=''
declare CursorIndexColumn cursor for
select col.name, ixc.is_descending_key, ixc.is_included_column
from sys.tables tb
inner join sys.indexes ix on tb.object_id=ix.object_id
inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
inner join sys.columns col on ixc.object_id =col.object_id and ixc.column_id=col.column_id
where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
order by ixc.index_column_id
open CursorIndexColumn
fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
while (@@fetch_status=0)
begin
if @IsIncludedColumn=0
set @IndexColumns=@IndexColumns + @ColumnName + case when @IsDescendingKey=1 then ' DESC, ' else ' ASC, ' end
else
set @IncludedColumns=@IncludedColumns + @ColumnName +', '
fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
end
close CursorIndexColumn
deallocate CursorIndexColumn
set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
-- print @IndexColumns
-- print @IncludedColumns
set @TSQLScripCreationIndex =''
set @TSQLScripDisableIndex =''
set @TSQLScripCreationIndex='CREATE '+ @is_unique +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+
case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'
if @is_disabled=1
set @TSQLScripDisableIndex= CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13)
print @TSQLScripCreationIndex
print @TSQLScripDisableIndex
fetch next from CursorIndex into @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName
end
close CursorIndex
deallocate CursorIndex