Script out all SQL Server Indexes in a Database using T-SQL

By:   |   Updated: 2014-12-18   |   Comments (28)   |   Related: More > Indexing


Problem

Sometimes as a DBA we need to generate a T-SQL script for dropping and creating indexes in our SQL Server databases. However, SQL Server Management Studio does not have an easy way via the wizard to complete these tasks at the same time. In this tip, we look at a creating a script to drop and create all SQL Server indexes for a SQL Server database.

Solution

As DBA we have to be proactive and be ready to recover our indexes in case:

  • The indexes are not in a source control system
  • The indexes are dropped
  • The indexes are corrupted
  • The indexes need to be recreated for another copy of the same database for testing, development or training purposes
  • There is a need to recreate the indexes on a different FILEGROUP

T-SQL Script to Drop All SQL Server Indexes

Another related task is dropping all SQL Server indexes in a test environment then recreating them. The following script allows you to generate a script to drop all regular non-unique indexes in a database:

DECLARE @SchemaName VARCHAR(256)DECLARE @TableName VARCHAR(256)
DECLARE @IndexName VARCHAR(256)
DECLARE @TSQLDropIndex VARCHAR(MAX)

DECLARE CursorIndexes CURSOR FOR
 SELECT schema_name(t.schema_id), t.name,  i.name 
 FROM sys.indexes i
 INNER JOIN sys.tables t ON t.object_id= i.object_id
 WHERE i.type>0 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 and (is_primary_key=0 and is_unique_constraint=0)

OPEN CursorIndexes
FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName

WHILE @@fetch_status = 0
BEGIN
 SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)
 PRINT @TSQLDropIndex
 FETCH NEXT FROM CursorIndexes INTO @SchemaName,@TableName,@IndexName
END

CLOSE CursorIndexes
DEALLOCATE CursorIndexes 

T-SQL Script to Create All SQL Server Indexes

This T-SQL code is designed to generate a script to create all regular non-unique indexes in a specific SQL Server database. This code queries the index metadata by using four views: sys.tables, sys.indexes, sys.index_columns and sys.columns. These views offer all the information needed to generate the scripts related to index creation.

Here are some things to be aware of when generating the creation script:

  • If the index was disabled, then the code to disable it will also be generated.
  • If the index has included columns then they will be included in the same order.
  • The creation script considers all properties of the index (FILEGROUP, ALLOW_PAGE_LOCKS, STATISTICS_NORECOMPUTE, FILLFACTOR, SORT_IN_TEMPDB, etc.). You will not lose any index properties, the final script will create the original index.
  • Will not script indexes linked to the primary key
  • Will not script partitioned indexes
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 [email protected]
 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)[email protected] and [email protected] and [email protected]
  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 @[email protected] + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @[email protected]  + @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  [email protected] + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('[email protected]+') '+ 
  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

 

Next Steps
  • Test this script in a test environment to validate all of your indexes are included.
  • Setup an automated process to script your database indexes on a regular basis if your databases are not in source control.
  • Read these additional related tips:





get scripts

next tip button



About the author
MSSQLTips author Percy Reyes Percy Reyes is a SQL Server MVP and Sr. Database Administrator focused on SQL Server Internals with over 10+ years of extensive experience managing critical database servers.

View all my tips


Article Last Updated: 2014-12-18

Comments For This Article




Thursday, September 15, 2022 - 6:16:27 PM - Michelle D Back To Top (90487)
Thanks Percy & GaryS, love your work. Here's a slightly modified version of your latest combined scripts, hope it helps someone :)


-- ********************* SAVE THIS AS GenerateDatabaseDropIndexScript.sql ************************

/*------------------------------------------------------------------------------------------------------
Source: https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/

Description: Generates a T-SQL Script to Drop All SQL Server Indexes including Primary Key Constraints

Written by: 18Dec2014 Percy Reyes
Modified by: 16Sep2022 MichelleD
Tidied up & formatted code. Formatted output script (eg: @TabSpaces). Added IF NOT EXISTS AND EXISTS & PRINTs & ORDER BY.
Changed to include Primary Keys and unique constraints. Added filters.
------------------------------------------------------------------------------------------------------*/

-- FILTERS - you can EDIT the values for these

DECLARE @FilterSchemaName VARCHAR(255) = '%' -- '%' = all, 'dbo'
,@FilterTableName VARCHAR(255) = '%' -- '%' = all
,@FilterIndexName VARCHAR(255) = '%' -- '%' = all, '%PK%'
,@FilterIndexType VARCHAR(50) = '%' -- '%' = all, 'CLUSTERED', 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED', 'NONCLUSTERED HASH', 'SPATIAL', 'XML'

------------------------------------------------------------------------------------------------------

DECLARE
-- Variables for CursorIndex
@SchemaName VARCHAR(256)
,@TableName VARCHAR(256)
,@IndexName VARCHAR(256)
,@IndexTypeDesc VARCHAR(100)
,@IsPrimaryKey BIT
,@IsUniqueConstraint BIT

-- Other Variables
,@TabSpaces VARCHAR(4)
,@TsqlScriptDropIndex VARCHAR(MAX)


SELECT @TabSpaces = ' ' -- used to simulate a tab to tidy up the output code

------------------------------------------------------------------------------------------------------

PRINT 'PRINT ''Starting ...'' ' + CHAR(13) + CHAR(13)


DECLARE CursorIndex CURSOR FOR

SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TableName
,i.name AS IndexName
,i.type_desc AS IndexTypeDesc
,i.is_primary_key AS IsPrimaryKey
,i.is_unique_constraint AS IsUniqueConstraint -- not actually used

FROM sys.indexes i

INNER JOIN sys.tables t
ON t.object_id = i.object_id
AND t.is_ms_shipped <> 1 -- ignore system tables

WHERE t.is_ms_shipped = 0 -- ignore system tables
AND t.name <> 'sysdiagrams'
AND i.name IS NOT NULL
AND i.type > 0 -- to ignore HEAPs
--AND i.is_primary_key = 0 -- to exclude PRIMARY KEY indexes
--AND i.is_unique_constraint = 0
AND SCHEMA_NAME(t.schema_id) LIKE @FilterSchemaName
AND t.name LIKE @FilterTableName
AND i.name LIKE @FilterIndexName
AND i.type_desc LIKE @FilterIndexType

ORDER BY
SCHEMA_NAME(t.schema_id)
,t.name
,i.is_primary_key -- sort primary keys lower
,i.name

OPEN CursorIndex
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @IsPrimaryKey, @IsUniqueConstraint
WHILE (@@fetch_status = 0)
BEGIN

-- Build the TSQL Script
SET @TsqlScriptDropIndex = 'IF EXISTS (SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID(''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ''') AND NAME = ''' + @IndexName + ''')'
+ CHAR(13)
+ 'BEGIN'
+ CHAR(13) + @TabSpaces
+ 'PRINT CONVERT(VARCHAR, GETDATE(), 120) + '': dropping '
+ CASE
WHEN @IsPrimaryKey = 1
THEN 'PRIMARY KEY constraint/index: '
ELSE + @IndexTypeDesc + ' index: '
END
+ QUOTENAME(@IndexName) + ''''
+ CHAR(13) + @TabSpaces
+ CASE
WHEN @IsPrimaryKey = 1
THEN 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@IndexName)
ELSE 'DROP INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
END
+ CHAR(13)
+ 'END'
+ CHAR(13) + CHAR(13)

-- Output the TSQL Script to the Messsages Window
PRINT @TsqlScriptDropIndex

FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @IsPrimaryKey, @IsUniqueConstraint

END
CLOSE CursorIndex
DEALLOCATE CursorIndex


PRINT 'PRINT ''Finished ...'' '

------------------------------------------------------------------------------------------------------





-- ********************* SAVE THIS AS GenerateDatabaseCreateIndexScript.sql ************************

/*------------------------------------------------------------------------------------------------------
Source: https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/
Based on post: Friday, July 8, 2016 - 11:48:41 AM - GaryS

Description: Generates a T-SQL Script to Create All SQL Server Indexes including Primary Key Constraints
Does not script out Extended Properties (comments), or full-text indexes (and catalogs).
For SQL Server 2014 and greater, however to work for SQL Server 2012 and earlier:
* search for 'SQL Server 2014' and comment out that code (4 sections)
* search for 'SQL Server 2012' and uncomment out that code (2 lines)

Written by: 18Dec2014 Percy Reyes
Modified by: 14Apr2016 GaryS
Edited to include index types: clustered; nonclustered; PK (not with an alter table statement but with a create unique statement); partitioned; and columnstore.
Added data compression, when applicable.
Modified by: 08Jul2016 GaryS
Include more index types and in doing so, split the query into two (SQL Server 2012 and earlier; SQL Server 2014 and later).
Modified to add ALTER TABLE for PK indexes and unique constraints, data compression, filtered indexes, XML indexes,
partitioned indexes, spatial indexes, indexes on memory-optimized tables (including hash indexes), and online options (when applicable).
Modified by: 16Sep2022 MichelleD
Tidied up & formatted code. Formatted output script (eg: @TabSpaces). Added IF NOT EXISTS AND EXISTS & PRINTs & ORDER BY.
Added SQL Server 2012 & 2014 comments to know what code to comment out & uncomment.
Added 'ix.type_desc NOT LIKE '%COLUMNSTORE%' to IndexOptions. Added filters.
------------------------------------------------------------------------------------------------------*/

-- Determine which version of SQL Server you have
/*
-- SQL Server 2008 and later
SELECT @@VERSION

-- SQL Server 2000 and later
SELECT
SERVERPROPERTY('MachineName') AS ComputerName
,SERVERPROPERTY('ServerName') AS InstanceName
,SERVERPROPERTY('Edition') AS Edition
,SERVERPROPERTY('ProductLevel') AS ProductLevel
,SERVERPROPERTY('ProductVersion') AS ProductVersion
,SERVERPROPERTY('ProductMajorVersion') AS ProductMajorVersion
,SERVERPROPERTY('ProductMinorVersion') AS ProductMinorVersion
,SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference
*/
------------------------------------------------------------------------------------------------------

-- FILTERS - you can EDIT the values for these

DECLARE @FilterSchemaName VARCHAR(255) = '%' -- '%' = all, 'dbo'
,@FilterTableName VARCHAR(255) = '%' -- '%' = all
,@FilterIndexName VARCHAR(255) = '%' -- '%' = all, '%PK%'
,@FilterIndexType VARCHAR(50) = '%' -- '%' = all, 'CLUSTERED', 'CLUSTERED COLUMNSTORE', 'NONCLUSTERED', 'NONCLUSTERED HASH', 'SPATIAL', 'XML'

------------------------------------------------------------------------------------------------------

DECLARE -- Variables for CursorIndex
@SchemaName VARCHAR(100)
,@TableName VARCHAR(256)
,@IndexName VARCHAR(256)
,@IsUnique VARCHAR(100)
,@IndexTypeDesc VARCHAR(100)
,@IndexOptions VARCHAR(MAX)
,@IsDisabled VARCHAR(100)
,@FileGroupName VARCHAR(100)
,@DataCompressionType VARCHAR(100)
,@IsFiltered BIT
,@FilterDefinition VARCHAR(MAX)
,@IsPrimaryKey BIT
,@IsUniqueConstraint BIT
,@CompressionDelay INT

-- Variables for CursorIndexColumn
,@ColumnName VARCHAR(100)
,@IsDescendingKey INT
,@IsIncludedColumn INT

-- Other Variables
,@TabSpaces VARCHAR(4)
,@TsqlScriptBeforeIndex VARCHAR(MAX)
,@TsqlScriptCreateIndex VARCHAR(MAX)
,@TsqlScriptDisableIndex VARCHAR(MAX)
,@TsqlScriptAfterIndex VARCHAR(MAX)
,@IndexColumns VARCHAR(MAX)
,@IncludedColumns VARCHAR(MAX)


SELECT @TabSpaces = ' ' -- used to simulate a tab to tidy up the output code

------------------------------------------------------------------------------------------------------

PRINT 'PRINT ''Starting ...'' ' + CHAR(13) + CHAR(13)


DECLARE CursorIndex CURSOR FOR

-- CTE to collect partitioned index information
WITH PartitionedIndexes AS (
SELECT
t.object_id AS ObjectID
,t.name AS TableName
,ic.column_id AS PartitioningColumnID
,c.name AS PartitioningColumnName
,s.name AS PartitionScheme
,ix.name AS IndexName
,ix.index_id

FROM sys.tables t

INNER JOIN sys.indexes i
ON i.object_id = t.object_id

INNER JOIN sys.index_columns ic
ON ic.index_id = i.index_id
AND ic.object_id = t.object_id

INNER JOIN sys.columns c
ON c.object_id = ic.object_id
AND c.column_id = ic.column_id

INNER JOIN sys.partition_schemes s
ON s.data_space_id = i.data_space_id

INNER JOIN sys.indexes ix
ON ix.object_id = t.object_id
AND ix.index_id = i.index_id

WHERE ic.partition_ordinal = 1 -- only want 1 record per index at this stage
AND t.is_ms_shipped = 0 -- ignore system tables

)
SELECT
SCHEMA_NAME(t.schema_id) AS SchemaName
,t.name AS TableName
,ix.name AS IndexName
,CASE
WHEN ix.is_unique = 1
THEN 'UNIQUE '
ELSE ''
END AS IsUnique
,CASE
WHEN t.object_id IN (SELECT object_id FROM sys.tables WHERE is_memory_optimized = 1)
AND ix.type_desc <> 'NONCLUSTERED HASH'
THEN 'MEMORY_OPTIMIZED'
ELSE ix.type_desc
END AS TypeDesc -- SQL Server 2014 & later
--,ix.type_desc AS TypeDesc -- SQL Server 2012 & earlier (is_memory_optimized doesn't exist)
,CASE
WHEN ix.is_padded = 1
THEN 'PAD_INDEX = ON, '
ELSE 'PAD_INDEX = OFF, '
END
+CASE
WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1
THEN 'STATISTICS_NORECOMPUTE = ON, '
ELSE 'STATISTICS_NORECOMPUTE = OFF, '
END
+CASE
WHEN ix.is_primary_key = 1
OR ix.is_unique_constraint = 1
THEN ''
ELSE 'SORT_IN_TEMPDB = OFF, '
END
+CASE
WHEN ix.ignore_dup_key = 1
THEN 'IGNORE_DUP_KEY = ON, '
ELSE 'IGNORE_DUP_KEY = OFF, '
END
+CASE
WHEN ix.type_desc NOT LIKE '%COLUMNSTORE%'
AND @@VERSION LIKE '%ENTERPRISE%'
AND ix.is_primary_key = 0
AND ix.is_unique_constraint = 0
AND ( (LOBTable.CanBeBuiltOnline IS NULL AND ix.type_desc = 'CLUSTERED')
OR (LOBIndexes.CanBeBuiltOnline IS NULL AND ix.type_desc <> 'CLUSTERED')
)
THEN 'ONLINE = ON, '
ELSE ''
END
+CASE
WHEN ix.allow_row_locks = 1
THEN 'ALLOW_ROW_LOCKS = ON, '
ELSE 'ALLOW_ROW_LOCKS = OFF, '
END
+CASE
WHEN ix.allow_page_locks = 1
THEN 'ALLOW_PAGE_LOCKS = ON, '
ELSE 'ALLOW_PAGE_LOCKS = OFF, '
END
+CASE
WHEN CAST(ix.fill_factor AS VARCHAR(3)) = 0
THEN ''
ELSE 'FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) + ', '
END
+CASE
WHEN p.data_compression_desc IS NULL
THEN 'DATA_COMPRESSION = NONE'
ELSE 'DATA_COMPRESSION = ' + p.data_compression_desc
END AS IndexOptions
,ix.is_disabled AS IsDisabled
,CASE
WHEN ic.IsColumnPartitioned = 1
THEN '[' + PIdx.PartitionScheme + ']' + '(' + '[' + PIdx.PartitioningColumnName + ']' + ')'
WHEN ic.IsColumnPartitioned = 0
THEN '[' + FILEGROUP_NAME(ix.data_space_id) + ']'
END AS FileGroupName
,ix.has_filter AS HasFilter
,ix.filter_definition AS FilterDefinition
,ix.is_primary_key AS IsPrimaryKey
,ix.is_unique_constraint AS IsUniqueConstraint
,ix.compression_delay AS CompressionDelay -- SQL Server 2014 and later
--,NULL AS CompressionDelay -- SQL Server 2012 and earlier (compression_delay doesn't exist)

FROM sys.tables t

INNER JOIN sys.indexes ix
ON ix.object_id = t.object_id

INNER JOIN (
SELECT DISTINCT
object_id
,index_id
,MAX(partition_ordinal) AS IsColumnPartitioned
FROM sys.index_columns
GROUP BY
object_id
,index_id
) ic
ON ic.index_id = ix.index_id
AND ic.object_id = t.object_id

LEFT OUTER JOIN (
SELECT DISTINCT
object_id
,index_id
,data_compression_desc
FROM sys.partitions
) p
ON p.object_id = ix.object_id
AND p.index_id = ix.index_id

LEFT OUTER JOIN PartitionedIndexes PIdx
ON PIdx.ObjectID = t.object_id
AND PIdx.index_id = ix.index_id

LEFT OUTER JOIN (
SELECT DISTINCT
c.object_id
,0 AS CanBeBuiltOnline
FROM sys.columns c
INNER JOIN sys.types t
ON t.user_type_id = c.user_type_id
WHERE t.Name IN ('image','ntext','text','XML')
OR ( t.Name IN ('VARCHAR','nVARCHAR','varbinary')
AND c.max_length = -1
)
OR c.is_filestream = 1
) LOBTable
ON LOBTable.object_id = t.object_id

LEFT OUTER JOIN (
SELECT DISTINCT c.object_id,
i.index_id,
0 AS CanBeBuiltOnline
FROM sys.columns c
INNER JOIN sys.types t
ON t.user_type_id = c.user_type_id
LEFT OUTER JOIN sys.index_columns ic
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
INNER JOIN sys.indexes i
ON i.object_id = ic.object_id
AND i.index_id = ic.index_id
WHERE t.Name IN ('image','ntext','text','XML')
OR ( t.Name IN ('VARCHAR','nVARCHAR','varbinary')
AND c.max_length = -1
)
OR c.is_filestream = 1
) LOBIndexes
ON LOBIndexes.object_id = t.object_id
AND LOBIndexes.index_id = ix.index_id

WHERE t.is_ms_shipped = 0 -- ignore system tables
AND t.name <> 'sysdiagrams'
AND ix.name IS NOT NULL
AND ix.type > 0 -- to ignore HEAPs
--AND ix.is_primary_key = 0 -- to ignore PRIMARY KEY indexes
--AND ix.is_unique_constraint = 0
AND SCHEMA_NAME(t.schema_id) LIKE @FilterSchemaName
AND t.name LIKE @FilterTableName
AND ix.name LIKE @FilterIndexName
AND ix.type_desc LIKE @FilterIndexType

ORDER BY
SCHEMA_NAME(t.schema_id)
,t.name
,CASE
WHEN ix.type_desc NOT LIKE '%COLUMNSTORE%'
AND @@VERSION LIKE '%ENTERPRISE%'
AND ix.is_primary_key = 0
AND ix.is_unique_constraint = 0
AND ( (LOBTable.CanBeBuiltOnline IS NULL AND ix.type_desc = 'CLUSTERED')
OR (LOBIndexes.CanBeBuiltOnline IS NULL AND ix.type_desc <> 'CLUSTERED')
)
THEN 0
ELSE 1
END -- sort 'ONLINE = ON' indexes higher
,CASE WHEN ix.is_primary_key = 1 THEN 0 ELSE 1 END -- sort primary keys higher
,CASE WHEN ix.type_desc LIKE '%COLUMNSTORE%' THEN 1 ELSE 0 END -- sort columnstores lower
,ix.name

OPEN CursorIndex
FETCH NEXT FROM CursorIndex INTO @SchemaName,@TableName,@IndexName,@IsUnique,@IndexTypeDesc,@IndexOptions,@IsDisabled,@FileGroupName,@IsFiltered,@FilterDefinition,@IsPrimaryKey,@IsUniqueConstraint,@CompressionDelay
WHILE (@@fetch_status = 0)
BEGIN

SELECT @IndexColumns = ''
,@IncludedColumns = ''
,@DataCompressionType = ''

---------------------------------------------------

DECLARE CursorIndexColumn CURSOR FOR

SELECT
col.name AS ColumnName
,ixc.is_descending_key AS IsDescendingKey
,ixc.is_included_column AS IsIncludedColumn

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 SCHEMA_NAME(tb.schema_id) = @SchemaName
AND tb.name = @TableName
AND ix.name = @IndexName
--AND ( ix.type > 0 AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0 ) -- to ignore PK indexes
--AND ix.type_desc <> 'CLUSTERED' -- to ignore CLUSTERED indexes
--AND ix.type_desc NOT LIKE '%COLUMNSTORE%' -- to ignore COLUMNSTORE indexes

ORDER BY
ixc.key_ordinal -- this is actually the genuine index column order, not ixc.index_column_id

OPEN CursorIndexColumn
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName,@IsDescendingKey,@IsIncludedColumn
WHILE (@@fetch_status = 0)
BEGIN

IF ( @IsIncludedColumn = 0
OR @IndexTypeDesc LIKE '%COLUMNSTORE%'
OR @IndexTypeDesc IN ('XML', 'spatial', 'NONCLUSTERED HASH')
)
SET @IndexColumns = CASE
WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%'
OR @IndexTypeDesc IN ('XML', 'spatial', 'NONCLUSTERED HASH')
THEN @IndexColumns + QUOTENAME(@ColumnName) + ', '
ELSE @IndexColumns + QUOTENAME(@ColumnName)
+CASE WHEN @IsDescendingKey = 1 THEN ' DESC, ' ELSE ' ASC, ' END
END
ELSE
SET @IncludedColumns = @IncludedColumns + QUOTENAME(@ColumnName) + ', '

FETCH NEXT FROM CursorIndexColumn INTO @ColumnName,@IsDescendingKey,@IsIncludedColumn

END
CLOSE CursorIndexColumn
DEALLOCATE CursorIndexColumn

---------------------------------------------------

-- Build the TSQL Script
SELECT @IndexColumns = SUBSTRING(@IndexColumns, 1, CASE WHEN LEN(@IndexColumns) = 0 THEN 0 ELSE LEN(@IndexColumns)-1 END)
,@IncludedColumns = CASE WHEN LEN(@IncludedColumns) > 0 THEN SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns)-1) ELSE '' END
,@DataCompressionType = SUBSTRING(@IndexOptions,CHARINDEX('DATA_COMPRESSION',@IndexOptions)+18,LEN(@IndexOptions))
,@TsqlScriptBeforeIndex = 'IF NOT EXISTS (SELECT * FROM SYS.INDEXES WHERE OBJECT_ID = OBJECT_ID(''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ''') AND NAME = ''' + @IndexName + ''')'
+ CHAR(13) + @TabSpaces
+ 'AND EXISTS (SELECT * FROM SYS.OBJECTS WHERE OBJECT_ID = OBJECT_ID(''' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '''))'
+ CHAR(13)
+ 'BEGIN '
+ CHAR(13) + @TabSpaces
+ 'PRINT CONVERT(VARCHAR, GETDATE(), 120) + '': creating '
+ CASE WHEN @IsPrimaryKey = 1 THEN '(PRIMARY KEY) ' ELSE '' END
+ @IndexTypeDesc + ' index: ' + QUOTENAME(@IndexName) + ''''

,@TsqlScriptAfterIndex = 'END ' + CHAR(13)
,@TsqlScriptDisableIndex = ''

SET @TsqlScriptCreateIndex = CASE
WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%'
THEN CASE
WHEN @IndexTypeDesc LIKE 'CLUSTERED%'
THEN
@TabSpaces
+ 'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ 'WITH (COMPRESSION_DELAY = ' + CONVERT(VARCHAR,@CompressionDelay) + ', DATA_COMPRESSION = ' + @DataCompressionType + ') ON ' + @FileGroupName + ';'
WHEN @IsFiltered = 1
THEN
@TabSpaces
+ 'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') ' + + CHAR(13) + @TabSpaces
+ 'WHERE ' + @FilterDefinition + CHAR(13) + @TabSpaces
+ 'WITH (COMPRESSION_DELAY = ' + CONVERT(VARCHAR,@CompressionDelay) + ', DATA_COMPRESSION = ' + @DataCompressionType + ') ON ' + @FileGroupName + ';'
ELSE
@TabSpaces
+ 'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') ' + CHAR(13) + @TabSpaces
+ 'WITH (COMPRESSION_DELAY = ' + CONVERT(VARCHAR,@CompressionDelay) + ', DATA_COMPRESSION = ' + @DataCompressionType + ') ON ' + @FileGroupName + ';'
END
WHEN @IndexTypeDesc = 'MEMORY_OPTIMIZED'
THEN CASE
WHEN @IsPrimaryKey = 1
THEN
@TabSpaces
+ 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + ' PRIMARY KEY NONCLUSTERED' + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ');'
WHEN @IsPrimaryKey = 0
THEN
@TabSpaces
+ 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @IsUnique + ' NONCLUSTERED' + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ');'
END
-- SQL Server 2014 and later (sys.hash_indexes doesn't exist prior to this)
WHEN @IndexTypeDesc = 'NONCLUSTERED HASH'
THEN CASE
WHEN @IsPrimaryKey = 1
THEN ( SELECT
@TabSpaces
+ 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY NONCLUSTERED HASH' + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') '
+ 'WITH ( BUCKET_COUNT = ' + CONVERT(VARCHAR,bucket_count) + ');'
FROM sys.hash_indexes
WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
AND name = @IndexName
)
WHEN @IsPrimaryKey = 0
THEN ( SELECT
@TabSpaces
+ 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @IndexTypeDesc + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') '
+ 'WITH ( BUCKET_COUNT = ' + CONVERT(VARCHAR,bucket_count) + ');'
FROM sys.hash_indexes
WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
AND name = @IndexName
)
END
-- SQL Server 2014 and later (xml_index_type_description doesn't exist prior to this)
WHEN @IndexTypeDesc = 'XML'
THEN CASE
WHEN EXISTS ( SELECT name
FROM sys.xml_indexes
WHERE xml_index_type_description = 'PRIMARY_XML'
AND name = @IndexName
AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
)
THEN
@TabSpaces
+ 'CREATE PRIMARY XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') ' + CHAR(13) + @TabSpaces
+ 'WITH (' + @IndexOptions+ ');'
WHEN EXISTS ( SELECT object_id, index_id, name
FROM sys.xml_indexes
WHERE xml_index_type_description <> 'PRIMARY_XML'
AND name = @IndexName
AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
)
THEN
( SELECT
@TabSpaces
+ 'CREATE XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ')' + CHAR(13) + @TabSpaces
+ 'USING XML INDEX ' + QUOTENAME(P.name)
+ ' FOR ' + I.secondary_type_desc COLLATE LATIN1_GENERAL_CS_AS
+ ' WITH (' + @IndexOptions+ ');'
FROM sys.xml_indexes I
INNER JOIN (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML') P
ON P.object_id = I.object_id AND P.index_id = I.using_xml_index_id AND I.name = @IndexName
)
END
WHEN @IndexTypeDesc = 'spatial'
THEN
( SELECT
@TabSpaces
+ 'CREATE SPATIAL INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ')'
+ 'USING ' + tessellation_scheme + CHAR(13) + @TabSpaces
+ 'WITH (BOUNDING_BOX =('
+ CONVERT(VARCHAR,bounding_box_xmin) + ', '
+ CONVERT(VARCHAR,bounding_box_ymin) + ', '
+ CONVERT(VARCHAR,bounding_box_xmax) + ', '
+ CONVERT(VARCHAR,bounding_box_ymax) + '), '
+ 'GRIDS =(LEVEL1 = ' + level_1_grid_desc + ',LEVEL_2 = ' + level_2_grid_desc + ',LEVEL3 = ' + level_3_grid_desc + ',LEVEL4 = ' + level_4_grid_desc + '), '
+ 'CELLS_PER_OBJECT = ' + CONVERT(VARCHAR,cells_per_object) + ', ' + @IndexOptions + ') ON ' + @FileGroupName + ';'
FROM sys.spatial_index_tessellations
WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
AND index_id = (SELECT index_id FROM sys.indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name = @IndexName)
)
WHEN @IsFiltered = 1
THEN
@TabSpaces
+ 'CREATE ' + @IsUnique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') '
+ CASE WHEN LEN(@IncludedColumns) > 0 THEN CHAR(13) + @TabSpaces + 'INCLUDE (' + @IncludedColumns + ')' ELSE '' END + CHAR(13) + @TabSpaces
+ 'WHERE ' + @FilterDefinition + CHAR(13) + @TabSpaces
+ 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'
WHEN @IsPrimaryKey = 1
THEN
@TabSpaces
+ 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY ' + @IndexTypeDesc
+ CHAR(13) + @TabSpaces + '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') ' + CHAR(13) + @TabSpaces
+ 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'
WHEN @IsUniqueConstraint = 1
THEN
@TabSpaces
+ 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' ADD CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + @IsUnique + @IndexTypeDesc + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') ' + CHAR(13) + @TabSpaces
+ 'WITH (' + @IndexOptions+ ');'
ELSE
@TabSpaces
+ 'CREATE '+ @IsUnique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + @TabSpaces
+ '(' + CHAR(13) + @TabSpaces + @TabSpaces + @IndexColumns + CHAR(13) + @TabSpaces + ') '
+ CASE WHEN LEN(@IncludedColumns) > 0 THEN CHAR(13) + @TabSpaces + 'INCLUDE (' + @IncludedColumns+ ')' ELSE '' END + CHAR(13) + @TabSpaces
+ 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'
END

IF ( @IsDisabled = 1 )
SET @TsqlScriptDisableIndex = @TabSpaces
+ 'PRINT CONVERT(VARCHAR, GETDATE(), 120) + '': disabling index: ' + QUOTENAME(@IndexName) + ''''
+ CHAR(13) + @TabSpaces
+ 'ALTER INDEX ' + QUOTENAME(@IndexName)
+ ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName)
+ ' DISABLE;'

---------------------------------------------------

-- Output the TSQL Script to the Messages Window

-- Comment
IF (@TsqlScriptCreateIndex LIKE '%ONLINE = ON%')
PRINT '-- NOTE: If a COLUMNSTORE index already exists, ONLINE = ON will cause an error. You will need to drop the COLUMNSTORE, add the ONLINE = ON, then add the COLUMNSTORE.'

-- Start IF block
PRINT @TsqlScriptBeforeIndex

-- Create/Alter Index/Constraint
PRINT @TsqlScriptCreateIndex
--PRINT @TabSpaces + 'GO;' -- can't call GO inside an IF block

-- Disable Index
IF (@TsqlScriptDisableIndex <> '')
--PRINT @TsqlScriptDisableIndex + @TabSpaces + 'GO;' -- can't call GO inside an IF block
PRINT @TsqlScriptDisableIndex

-- End IF block
PRINT @TsqlScriptAfterIndex + CHAR(10) + CHAR(10)

---------------------------------------------------

FETCH NEXT FROM CursorIndex INTO @SchemaName,@TableName,@IndexName,@IsUnique,@IndexTypeDesc,@IndexOptions,@IsDisabled,@FileGroupName,@IsFiltered,@FilterDefinition,@IsPrimaryKey,@IsUniqueConstraint,@CompressionDelay

END
CLOSE CursorIndex
DEALLOCATE CursorIndex


PRINT 'PRINT ''Finished ...'' '

------------------------------------------------------------------------------------------------------

Tuesday, June 21, 2022 - 6:05:01 AM - Исраэль Back To Top (90176)
Thank you.

Thursday, February 10, 2022 - 4:30:07 AM - KK Back To Top (89770)
This helps me a lot, thank you very much!

Thursday, May 13, 2021 - 12:09:50 PM - Bob Back To Top (88681)
Thanks very much to Percy and Gary S for sharing these scripts.
Most helpful.

Wednesday, September 18, 2019 - 7:15:10 AM - Bhote Back To Top (82493)

Teriffic! Thank you.


Monday, June 3, 2019 - 2:41:52 PM - meg Back To Top (81311)

For the index create script, the generated column sort order does not equal the original. It looks like the script needs an adjustment to sort the index in the same order as the original.


Tuesday, October 30, 2018 - 4:45:49 PM - Daniel Gras Back To Top (78116)

 Thanks, this allowed me to move over 100 objects back to the primary filegroup.


Wednesday, October 17, 2018 - 9:14:14 AM - Nikolay Karulin Back To Top (77971)

 Hi Percy,

what do you mean under "indexes linked to the primary key"?

Thanks,

 


Wednesday, August 2, 2017 - 9:12:53 AM - jschenck Back To Top (63765)

 

Thank you!   This has really helped me to capture all the indexes that have been created by _dta_ or other custom indexes so I can re-define them or move them to different filegroups.

I did have to make a code change to address "Invalid length parameter passed to the LEFT or SUBSTRING function" errors. 

 set @IndexColumns = substring(@IndexColumns, 1, 

     case when  len(@IndexColumns) = 0 then LEN(@IndexColumns) 

     else len(@IndexColumns) -1 end)

 


Monday, June 19, 2017 - 5:25:45 AM - Ethan Shalev Back To Top (57735)

 I built upon Cristi Boboc's code to make it also support columnstore indexes:
(also, the code needed some fixing of its own...)

SELECT 'CREATE ' +
CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
(i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS) +
' INDEX ' +
QUOTENAME(i.[name]) +
' ON ' +
QUOTENAME(schema_name(t.schema_id)) +
'.' +
QUOTENAME(t.[name]) + char(10)+

REPLACE(REPLACE(REPLACE((SELECT QUOTENAME(col_name(object_id, column_id)) + CASE WHEN c.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END + char(10) x
FROM [sys].[index_columns] c
WHERE c.[object_id] = i.[object_id] AND
c.[index_id]  = i.[index_id]  AND
c.[is_included_column] = 0
ORDER BY c.[index_column_id]
FOR XML PATH('')), '', ', '), '', ')'), '', '(') + char(10) +

COALESCE(' INCLUDE ' + char(10) + REPLACE(REPLACE(REPLACE((SELECT QUOTENAME(col_name(object_id, column_id)) + char(10) x
FROM [sys].[index_columns] c
WHERE c.[object_id] = i.[object_id] AND
c.[index_id]  = i.[index_id]  AND c.[is_included_column] = 1 ORDER BY c.[index_column_id] FOR XML PATH('')), '', ', '), '', ')'), '', '('), '') + char(10) +
' WITH (' + CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END +
CASE WHEN i.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END +
CASE WHEN i.[allow_row_locks]  = 1 THEN 'ALLOW_ROW_LOCKS = ON,  ' ELSE 'ALLOW_ROW_LOCKS = OFF,  ' END +
CASE WHEN INDEXPROPERTY(t.object_id, i.[name], 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE
'STATISTICS_NORECOMPUTE = OFF, ' END +
CASE WHEN i.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END +
'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(i.fill_factor AS VARCHAR(3)) + ') ON ' +
QUOTENAME(FILEGROUP_NAME(i.data_space_id)) +
';' as INDEX_CREATE_SCRIPT,
schema_name(t.schema_id) as [schema_name],
t.[name] as Table_Name,
i.[name] as Index_name,
CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END  as ISUNIQUE,
i.type_desc,
CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END +
CASE WHEN i.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END +
CASE WHEN i.[allow_row_locks]  = 1 THEN 'ALLOW_ROW_LOCKS = ON,  ' ELSE 'ALLOW_ROW_LOCKS = OFF,  ' END +
CASE WHEN INDEXPROPERTY(t.object_id, i.[name], 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END +
CASE WHEN i.[ignore_dup_key] = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END +
'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(i.fill_factor AS VARCHAR(3)) AS IndexOptions,
i.is_disabled,
FILEGROUP_NAME(i.data_space_id) FileGroupName
FROM [sys].[tables] t JOIN
[sys].[indexes] i ON t.object_id = i.object_id
WHERE i.[type] > 0 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND t.name like 'Mrr_%' --AND schema_name(t.schema_id)= @SchemaName AND [email protected] AND t.is_ms_shipped=0 AND t.name<>'sysdiagrams'
and INDEXPROPERTY(t.object_id, i.[name], 'IsColumnstore') = 0

UNION ALL

SELECT 'CREATE ' +
CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
(i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS) +
' INDEX ' +
QUOTENAME(i.[name]) +
' ON ' +
QUOTENAME(schema_name(t.schema_id)) +
'.' +
QUOTENAME(t.[name]) + char(10)+
' WITH (DROP_EXISTING = OFF, ' +
CASE WHEN i.[compression_delay] = 0 THEN 'COMPRESSION_DELAY = 0, ' ELSE 'COMPRESSION_DELAY = ' + CAST(i.[compression_delay] AS VARCHAR(4)) + ' MINUTES, ' END +
'DATA_COMPRESSION = ' + p.data_compression_desc + ') ON ' +
QUOTENAME(FILEGROUP_NAME(i.data_space_id)) +
';' as INDEX_CREATE_SCRIPT,
schema_name(t.schema_id) [schema_name],
t.[name] as Table_Name,
i.[name] as Index_name,
CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END as ISUNIQUE,
i.type_desc,
'DROP_EXISTING = OFF, ' +
CASE WHEN i.[compression_delay] = 0 THEN 'COMPRESSION_DELAY = 0, ' ELSE 'COMPRESSION_DELAY = ' + CAST(i.[compression_delay] AS VARCHAR(4)) + ' MINUTES, ' END +
'DATA_COMPRESSION = ' + p.data_compression_desc AS IndexOptions,
i.is_disabled,
FILEGROUP_NAME(i.data_space_id) FileGroupName
FROM [sys].[tables]  t JOIN
[sys].[indexes] i ON t.object_id = i.object_id join
[sys].[partitions] p ON p.object_Id = i.object_id and p.index_id = i.index_id
WHERE i.[type] > 0 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 AND t.name like 'Mrr_%' --AND schema_name(t.schema_id)= @SchemaName AND [email protected] AND t.is_ms_shipped=0 AND t.name<>'sysdiagrams'
and INDEXPROPERTY(t.object_id, i.[name], 'IsColumnstore') = 1

ORDER BY schema_name(t.schema_id),
t.[name],
i.[name]

I hope I haven't missed anything. If I did, I'd be happy to know!


Wednesday, May 31, 2017 - 4:51:13 PM - Don Back To Top (56370)

First; thank you! This saved me a TON of time!

Second; When I tested the output I got an error "Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100.".

Most of my indexes do not have the fill factor set so I added another case statement for the fill_factor:

"+ case when ix.fill_factor>0 then 'FILLFACTOR = '+ CAST(ix.fill_factor AS VARCHAR(3)) + ', ' else '' end"

(I also removed the "FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3))")

Third;  I added a GO between the create statements

" + CHAR(13)+'GO'"

(this was at the end of the "set @TSQLScripCreationIndex='CREATE '+..." statement

Fourth;     THANKS AGAIN!


Friday, January 6, 2017 - 4:20:30 PM - JP Back To Top (45173)

 Just a little detail, quoting the column names with has made the scrip bulletproof for me.

Thanks a lot

...

 declare CursorIndexColumn cursor for 

  select '[' + col.name + ']' as name

 ...


Friday, December 9, 2016 - 9:30:31 AM - Wendel Back To Top (44932)

 Hi Percy, Very Very, Thank You.

 

 


Monday, November 21, 2016 - 1:18:45 PM - 3N1GM4 Back To Top (43816)

With regards to the "Arithmetic overflow error for data type smallint" issue mentioned by Jensky, this is due to a mismatch between the types of sys.indexes.data_space_id - which is int - and the filegroup_id parameter passed to FILEGROUP_NAME() - which is smallint.

To avoid this issue, add the following statement to the WHERE clause of the definition of CursorIndex:

AND ix.data_space_id <= 32767

this will exclude any indexes which will otherwise cause the arithmetic overflow error.


Monday, August 22, 2016 - 8:49:43 AM - Jenksy Back To Top (43160)

 The code you provided to script all indexes did not work for me.  I received the following error:

Msg 220, Level 16, State 1, Line 85

Arithmetic overflow error for data type smallint, value = 65604.

This error points to fetching the next CursorIndex, and complains about a SmallInt, but there are no SmallInt's defined for this script.

 

 

 

 


Friday, July 22, 2016 - 1:15:42 PM - Shibu Shaji Back To Top (41952)

 Tnaks Percy. This saved me a lot of time.

 


Friday, July 8, 2016 - 11:48:41 AM - GaryS Back To Top (41842)

I have updated my previously posted query to include more index types and in doing so, split the query into two (one for SQL Server 2012 and earlier and one for SQL Server 2014 and later). Again, thank you for writing this post!

 

SQL Server 2014 and later:

 

/*

 

This script will generate the SQL to create all indexes in the database of SQL Server 2014 and greater.

 

Copied from https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/.

 

Modified by GaryS 4-14-2016.

 

Modified to add ALTER TABLE for PK indexes and unique constraints, data compression, filtered indexes, XML indexes,

 

partitioned indexes, spatial indexes, indexes on memory-optimized tables (including hash indexes), and online options (when applicable).

 

NOTE: Does not script out Extended Properties (comments), or full-text indexes (and catalogs).

 

*/

 

 

 

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 @DataCompressionType varchar(100);

 

DECLARE @is_filtered bit;

 

DECLARE @filter_definition varchar(MAX);

 

DECLARE @is_primary_key bit;

 

DECLARE @is_unique_constraint bit;

 

DECLARE @IndexColumnId int; 

 

DECLARE @IsDescendingKey int;

 

DECLARE @IsIncludedColumn int; 

 

DECLARE @compression_delay int;

 

DECLARE @TSQLScripCreationIndex varchar(MAX); 

 

DECLARE @TSQLScripDisableIndex varchar(MAX); 

 

 

 

DECLARE CursorIndex

 

 CURSOR

 

 FOR 

 

--CTE to collect partitioned index information

 

WITH PartitionedIndexes AS ( 

 

SELECT t.object_id Object_ID,

 

          t.name TableName,

 

          ic.column_id PartitioningColumnID,

 

          c.name PartitioningColumnName,

 

          s.name AS [partition_scheme],

 

          ix.name AS IndexName,

 

          ix.index_id

 

  FROM sys.tables t

 

  INNER JOIN sys.indexes i ON i.object_id = t.object_id

 

  INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id

 

  AND ic.object_id = t.object_id

 

  INNER JOIN sys.columns c ON c.object_id = ic.object_id

 

  AND c.column_id = ic.column_id

 

  INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

 

  INNER JOIN sys.indexes ix ON t.object_id=ix.object_id

 

  AND ix.index_id = i.index_id

 

  WHERE ic.partition_ordinal = 1

 

)

 

 

 

SELECT schema_name(t.schema_id) [schema_name],

 

      t.name TableName,

 

      ix.name IndexName,

 

      CASE

 

          WHEN ix.is_unique = 1 THEN 'UNIQUE '

 

          ELSE ''

 

      END IsUnique,

 

      CASE

 

WHEN t.object_id IN (SELECT OBJECT_ID FROM sys.tables WHERE is_memory_optimized = 1)

 

AND ix.type_desc <> 'NONCLUSTERED HASH'

 

THEN 'MEMORY_OPTIMIZED'

 

ELSE ix.type_desc

 

  END AS 

 

  type_desc,

 

      CASE

 

          WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, '

 

          ELSE 'PAD_INDEX = OFF, '

 

      END + 

 

  CASE

 

          WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, '

 

          ELSE 'STATISTICS_NORECOMPUTE = OFF, '

 

      END +

 

  CASE 

 

          WHEN (ix.is_primary_key = 1 OR ix.is_unique_constraint = 1) THEN ''

 

          ELSE 'SORT_IN_TEMPDB = OFF, ' 

 

      END +

 

  CASE

 

          WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, '

 

          ELSE 'IGNORE_DUP_KEY = OFF, '

 

      END + 

 

  CASE

 

  WHEN (@@Version LIKE '%ENTERPRISE%' AND (LOBTable.CanBeBuiltOnline IS NULL AND ix.type_desc = 'CLUSTERED') 

 

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0)

 

OR (@@Version LIKE '%ENTERPRISE%' AND (LOBIndexes.CanBeBuiltOnline IS NULL AND ix.type_desc <> 'CLUSTERED') 

 

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0) 

 

THEN 'ONLINE = ON, '

 

  ELSE '' 

 

      END +

 

  CASE

 

           WHEN ix.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, '

 

           ELSE 'ALLOW_ROW_LOCKS = OFF, '

 

      END +          

 

  CASE

 

           WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, '

 

           ELSE 'ALLOW_PAGE_LOCKS = OFF, '

 

      END +   

 

  CASE

 

          WHEN CAST(ix.fill_factor AS VARCHAR(3)) = 0 THEN ''

 

          ELSE 'FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) + ', '

 

      END + 

 

  CASE

 

          WHEN p.data_compression_desc IS NULL THEN 'DATA_COMPRESSION = NONE'

 

          ELSE 'DATA_COMPRESSION = ' + p.data_compression_desc

 

      END

 

  AS IndexOptions,

 

      ix.is_disabled,

 

      CASE

 

          WHEN ic.IsColumnPartitioned = 1 THEN '[' + PIdx.[partition_scheme] + ']' + '(' + '[' + PIdx.PartitioningColumnName + ']' + ')'

 

          WHEN ic.IsColumnPartitioned = 0 THEN '[' + FILEGROUP_NAME(ix.data_space_id) + ']'

 

      END AS FileGroupName,

 

  ix.has_filter,

 

  ix.filter_definition,

 

  ix.is_primary_key,

 

  ix.is_unique_constraint,

 

  ix.compression_delay

 

FROM sys.tables t

 

INNER JOIN sys.indexes ix ON t.object_id = ix.object_id

 

INNER JOIN

 

 (SELECT DISTINCT OBJECT_ID,

 

                  index_id,

 

                  MAX(partition_ordinal) AS IsColumnPartitioned

 

  FROM sys.index_columns

 

  GROUP BY OBJECT_ID,

 

           index_id) ic ON ic.index_id = ix.index_id

 

AND ic.object_id = t.object_id

 

LEFT JOIN

 

 (SELECT DISTINCT object_id,

 

                  index_id,

 

                  data_compression_desc

 

  FROM sys.partitions) p ON ix.object_id = p.object_id

 

AND ix.index_id = p.index_id

 

LEFT JOIN PartitionedIndexes PIdx ON PIdx.Object_ID = t.object_id

 

AND PIdx.index_id = ix.index_id

 

LEFT JOIN 

 

(SELECT DISTINCT object_id,

 

                0 AS CanBeBuiltOnline

 

FROM sys.columns c

 

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

 

WHERE t.Name IN ('image',

 

                'ntext',

 

                'text',

 

                'XML')

 

 OR (t.Name IN ('varchar',

 

                'nvarchar',

 

                'varbinary')

 

     AND c.max_length = -1)

 

 OR c.is_filestream = 1) LOBTable ON LOBTable.object_id = t.object_id

 

LEFT JOIN 

 

(SELECT DISTINCT c.object_id,

 

            i.index_id,

 

            0 AS CanBeBuiltOnline

 

FROM sys.columns c

 

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

 

LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id

 

AND ic.column_id = c.column_id

 

INNER JOIN sys.indexes i ON ic.object_id = i.object_id

 

AND ic.index_id = i.index_id

 

WHERE t.Name IN ('image',

 

                'ntext',

 

                'text',

 

                'XML')

 

 OR (t.Name IN ('varchar',

 

                'nvarchar',

 

                'varbinary')

 

     AND c.max_length = -1)

 

 OR c.is_filestream = 1) LOBIndexes ON LOBIndexes.object_id = t.object_id 

 

AND LOBIndexes.index_id = ix.index_id

 

WHERE  

 

/*****************Ignores PK indexes************************/

 

--ix.type > 0 AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0 AND

 

/***********************************************************/

 

t.is_ms_shipped = 0

 

 AND t.name <> 'sysdiagrams'

 

 AND ix.name IS NOT NULL

 

/*****************Ignores clustered indexes*****************/

 

--AND ix.type_desc <> 'CLUSTERED'

 

/***********************************************************/

 

/*****************Ignores COLUMNSTORE indexes***************/

 

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

 

/***********************************************************/

 

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,

 

@is_filtered,

 

@filter_definition,

 

@is_primary_key,

 

@is_unique_constraint,

 

@compression_delay

 

WHILE (@@fetch_status = 0) 

 

BEGIN 

 

DECLARE @IndexColumns varchar(MAX); 

 

DECLARE @IncludedColumns varchar(MAX);

 

SET @IndexColumns='';

 

SET @IncludedColumns=''; 

 

SET @DataCompressionType = '';

 

 

 

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 

 

/*****************Ignores PK indexes************************/

 

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

 

/*****************Ignores clustered indexes*****************/

 

--AND ix.type_desc <> 'CLUSTERED'

 

/***********************************************************/

 

/*****************Ignores COLUMNSTORE indexes***************/

 

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

 

    /***********************************************************/

 

ORDER BY ixc.key_ordinal

 

 

 

OPEN CursorIndexColumn 

 

FETCH NEXT

 

FROM CursorIndexColumn 

 

INTO @ColumnName,

 

        @IsDescendingKey,

 

        @IsIncludedColumn

 

   WHILE (@@fetch_status=0) 

 

BEGIN 

 

IF @IsIncludedColumn = 0 

 

OR @IndexTypeDesc LIKE '%COLUMNSTORE%' OR @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial' 

 

OR @IndexTypeDesc = 'NONCLUSTERED HASH'

 

SET @IndexColumns =

 

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' 

 

THEN @IndexColumns + '[' + @ColumnName + '], '

 

WHEN @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial' OR @IndexTypeDesc = 'NONCLUSTERED HASH'

 

THEN @IndexColumns + '[' + @ColumnName + '], '

 

ELSE @IndexColumns + '[' + @ColumnName + ']' +

 

CASE

 

WHEN @IsDescendingKey = 1 THEN ' DESC, '

 

ELSE ' ASC, '

 

               END 

 

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;

 

 

 

SET @TSQLScripCreationIndex = '';

 

SET @TSQLScripDisableIndex = '';

 

SET @DataCompressionType = SUBSTRING(@IndexOptions,CHARINDEX('DATA_COMPRESSION',@IndexOptions)+18,LEN(@IndexOptions));

 

SET @TSQLScripCreationIndex =

 

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' THEN

 

CASE 

 

WHEN @IndexTypeDesc LIKE 'CLUSTERED%' THEN 

 

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) +

 

'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +  

 

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType))  + ')

 

@DataCompressionType + ') ON ' + @FileGroupName + ';'

 

WHEN @is_filtered = 1 THEN

 

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

 

'' + @IndexColumns + CHAR(13) + ') ' + + CHAR(13) +

 

'WHERE ' + @filter_definition + CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' + 

 

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType))  + ')

 

@DataCompressionType + ') ON ' + @FileGroupName + ';'

 

ELSE

 

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

 

'' + @IndexColumns + CHAR(13) + ') ' + 

 

CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +  

 

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType))  + ')

 

@DataCompressionType + ') ON ' + @FileGroupName + ';' END

 

WHEN @IndexTypeDesc = 'MEMORY_OPTIMIZED' THEN

 

CASE WHEN @is_primary_key = 1 THEN 

 

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + ' PRIMARY KEY NONCLUSTERED'

 

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'

 

WHEN @is_primary_key = 0 THEN

 

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @is_unique + ' NONCLUSTERED'

 

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'END

 

WHEN @IndexTypeDesc = 'NONCLUSTERED HASH' THEN 

 

CASE WHEN @is_primary_key = 1 THEN 

 

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY NONCLUSTERED HASH'

 

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

 

FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name =  @IndexName)

 

WHEN @is_primary_key = 0 THEN 

 

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @IndexTypeDesc

 

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

 

       FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name =  @IndexName) END

 

WHEN @IndexTypeDesc = 'XML' THEN

 

CASE WHEN EXISTS (SELECT name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML' 

 

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN 

 

'CREATE PRIMARY XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

 

'' + @IndexColumns + CHAR(13) + ') ' +  CHAR(13) + 'WITH (' + @IndexOptions+ ');'

 

WHEN EXISTS (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description <> 'PRIMARY_XML' 

 

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN

 

(SELECT 'CREATE XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

 

'' + @IndexColumns + CHAR(13) + ')' + CHAR(13) + 'USING XML INDEX ' + QUOTENAME(P.name) 

 

+ ' FOR ' + I.secondary_type_desc collate latin1_general_cs_as  + ' WITH (' + @IndexOptions+ ');'

 

FROM sys.xml_indexes I

 

INNER JOIN (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML') P 

 

ON P.object_id = I.object_id AND P.index_id = I.using_xml_index_id AND I.name = @IndexName) 

 

END

 

WHEN @IndexTypeDesc = 'spatial' THEN 

 

(SELECT 'CREATE SPATIAL INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

 

'' + @IndexColumns + CHAR(13) + ')' + 'USING ' + tessellation_scheme + CHAR(13) 

 

+ 'WITH (BOUNDING_BOX =(' + CONVERT(varchar,bounding_box_xmin) + ', ' + CONVERT(varchar,bounding_box_ymin) + ', ' 

 

+ CONVERT(varchar,bounding_box_xmax) + ', ' + CONVERT(varchar,bounding_box_ymax)

 

+ '), GRIDS =(LEVEL1 = ' + level_1_grid_desc + ',LEVEL_2 = ' + level_2_grid_desc + ',LEVEL3 = ' + 

 

level_3_grid_desc + ',LEVEL4 = ' + level_4_grid_desc + '),

 

CELLS_PER_OBJECT = ' + CONVERT(varchar,cells_per_object) + ', ' + @IndexOptions + ') ON ' + @FileGroupName + ';' 

 

FROM sys.spatial_index_tessellations

 

WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND index_id = 

 

(SELECT index_id from sys.indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name =  @IndexName))

 

WHEN @is_filtered = 1 THEN

 

'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) ++ '(' + CHAR(13) + 

 

'' + @IndexColumns + CHAR(13) + ') ' +  

 

CASE

 

                                   WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

 

                                   ELSE ''

 

                               END + 

 

CHAR(13) + 'WHERE ' + @filter_definition + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

 

WHEN @is_primary_key = 1 THEN 

 

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY ' + @IndexTypeDesc

 

+ CHAR(13) + '(' + CHAR(13) + 

 

'' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

 

WHEN @is_unique_constraint = 1 THEN 

 

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + @is_unique + @IndexTypeDesc

 

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' +  CHAR(13) + 'WITH (' + @IndexOptions+ ');'

 

ELSE

 

'CREATE '+ @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

 

'' + @IndexColumns + CHAR(13) + ') ' +  

 

CASE

 

                                   WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

 

                                   ELSE ''

 

                               END + 

 

CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';' 

 

   END;

 

IF @is_disabled = 1

 

SET @TSQLScripDisableIndex = CHAR(13) +'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) + 'GO' + + CHAR(13) 

 

 

 

PRINT @TSQLScripCreationIndex;

 

PRINT 'GO'; 

 

PRINT @TSQLScripDisableIndex;

 

 

 

FETCH NEXT

 

FROM CursorIndex 

 

INTO @SchemaName,

 

    @TableName,

 

    @IndexName,

 

    @is_unique,

 

    @IndexTypeDesc,

 

    @IndexOptions,

 

    @is_disabled,

 

    @FileGroupName,

 

@is_filtered,

 

@filter_definition,

 

@is_primary_key,

 

@is_unique_constraint,

 

@compression_delay 

 

END;

 

CLOSE CursorIndex;

 

DEALLOCATE CursorIndex;

 

 

 

 

SQL Server 2012 and earlier:

 

/*

This script will generate the SQL to create all indexes in the database.

Copied from https://www.mssqltips.com/sqlservertip/3441/script-out-all-sql-server-indexes-in-a-database-using-tsql/.

Modified by GaryS 4-14-2016.

Modified to add ALTER TABLE for PK indexes and unique constraints, data compression, filtered indexes, XML indexes,

partitioned indexes, spatial indexes, and online options(when applicable).

NOTE: Does not script out Extended Properties (comments), or full-text indexes (and catalogs).

Also, due to several columns missing on older versions of SQL Server, I have commented out lines that only apply to 

SQL Server 2014 or greater.

*/

 

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 @DataCompressionType varchar(100);

DECLARE @is_filtered bit;

DECLARE @filter_definition varchar(MAX);

DECLARE @is_primary_key bit;

DECLARE @is_unique_constraint bit;

DECLARE @IndexColumnId int; 

DECLARE @IsDescendingKey int;

DECLARE @IsIncludedColumn int; 

DECLARE @compression_delay int;

DECLARE @TSQLScripCreationIndex varchar(MAX); 

DECLARE @TSQLScripDisableIndex varchar(MAX); 

 

DECLARE CursorIndex

 CURSOR

 FOR 

--CTE to collect partitioned index information

WITH PartitionedIndexes AS ( 

SELECT t.object_id Object_ID,

          t.name TableName,

          ic.column_id PartitioningColumnID,

          c.name PartitioningColumnName,

          s.name AS [partition_scheme],

          ix.name AS IndexName,

          ix.index_id

  FROM sys.tables t

  INNER JOIN sys.indexes i ON i.object_id = t.object_id

  INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id

  AND ic.object_id = t.object_id

  INNER JOIN sys.columns c ON c.object_id = ic.object_id

  AND c.column_id = ic.column_id

  INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

  INNER JOIN sys.indexes ix ON t.object_id=ix.object_id

  AND ix.index_id = i.index_id

  WHERE ic.partition_ordinal = 1

)

 

SELECT schema_name(t.schema_id) [schema_name],

      t.name TableName,

      ix.name IndexName,

      CASE

          WHEN ix.is_unique = 1 THEN 'UNIQUE '

          ELSE ''

      END IsUnique,

      /*

  --Commented out for older versions of SQL Server

      CASE

WHEN t.object_id IN (SELECT OBJECT_ID FROM sys.tables WHERE is_memory_optimized = 1)

AND ix.type_desc <> 'NONCLUSTERED HASH'

THEN 'MEMORY_OPTIMIZED'

ELSE*/ ix.type_desc

  --END

  AS type_desc,

      CASE

          WHEN ix.is_padded=1 THEN 'PAD_INDEX = ON, '

          ELSE 'PAD_INDEX = OFF, '

      END + 

  CASE

          WHEN INDEXPROPERTY(t.object_id, ix.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, '

          ELSE 'STATISTICS_NORECOMPUTE = OFF, '

      END +

  CASE 

          WHEN (ix.is_primary_key = 1 OR ix.is_unique_constraint = 1) THEN ''

          ELSE 'SORT_IN_TEMPDB = OFF, ' 

      END +

  CASE

          WHEN ix.ignore_dup_key=1 THEN 'IGNORE_DUP_KEY = ON, '

          ELSE 'IGNORE_DUP_KEY = OFF, '

      END + 

  CASE

  WHEN (@@Version LIKE '%ENTERPRISE%' AND (LOBTable.CanBeBuiltOnline IS NULL AND ix.type_desc = 'CLUSTERED') 

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0)

OR (@@Version LIKE '%ENTERPRISE%' AND (LOBIndexes.CanBeBuiltOnline IS NULL AND ix.type_desc <> 'CLUSTERED') 

AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0) 

THEN 'ONLINE = ON, '

  ELSE '' 

      END +

  CASE

           WHEN ix.allow_row_locks=1 THEN 'ALLOW_ROW_LOCKS = ON, '

           ELSE 'ALLOW_ROW_LOCKS = OFF, '

      END +          

  CASE

           WHEN ix.allow_page_locks=1 THEN 'ALLOW_PAGE_LOCKS = ON, '

           ELSE 'ALLOW_PAGE_LOCKS = OFF, '

      END +   

  CASE

          WHEN CAST(ix.fill_factor AS VARCHAR(3)) = 0 THEN ''

          ELSE 'FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) + ', '

      END + 

  CASE

          WHEN p.data_compression_desc IS NULL THEN 'DATA_COMPRESSION = NONE'

          ELSE 'DATA_COMPRESSION = ' + p.data_compression_desc

      END

  AS IndexOptions,

      ix.is_disabled,

      CASE

          WHEN ic.IsColumnPartitioned = 1 THEN '[' + PIdx.[partition_scheme] + ']' + '(' + '[' + PIdx.PartitioningColumnName + ']' + ')'

          WHEN ic.IsColumnPartitioned = 0 THEN '[' + FILEGROUP_NAME(ix.data_space_id) + ']'

      END AS FileGroupName,

  ix.has_filter,

  ix.filter_definition,

  ix.is_primary_key,

  ix.is_unique_constraint

  /*

  --Commented out for older versions of SQL Server

  ,ix.compression_delay*/

FROM sys.tables t

INNER JOIN sys.indexes ix ON t.object_id = ix.object_id

INNER JOIN

 (SELECT DISTINCT OBJECT_ID,

                  index_id,

                  MAX(partition_ordinal) AS IsColumnPartitioned

  FROM sys.index_columns

  GROUP BY OBJECT_ID,

           index_id) ic ON ic.index_id = ix.index_id

AND ic.object_id = t.object_id

LEFT JOIN

 (SELECT DISTINCT object_id,

                  index_id,

                  data_compression_desc

  FROM sys.partitions) p ON ix.object_id = p.object_id

AND ix.index_id = p.index_id

LEFT JOIN PartitionedIndexes PIdx ON PIdx.Object_ID = t.object_id

AND PIdx.index_id = ix.index_id

LEFT JOIN 

(SELECT DISTINCT object_id,

                0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE t.Name IN ('image',

                'ntext',

                'text',

                'XML')

 OR (t.Name IN ('varchar',

                'nvarchar',

                'varbinary')

     AND c.max_length = -1)

 OR c.is_filestream = 1) LOBTable ON LOBTable.object_id = t.object_id

LEFT JOIN 

(SELECT DISTINCT c.object_id,

            i.index_id,

            0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

INNER JOIN sys.indexes i ON ic.object_id = i.object_id

AND ic.index_id = i.index_id

WHERE t.Name IN ('image',

                'ntext',

                'text',

                'XML')

 OR (t.Name IN ('varchar',

                'nvarchar',

                'varbinary')

     AND c.max_length = -1)

 OR c.is_filestream = 1) LOBIndexes ON LOBIndexes.object_id = t.object_id 

AND LOBIndexes.index_id = ix.index_id

WHERE  

/*****************Ignores PK indexes************************/

--ix.type > 0 AND ix.is_primary_key = 0 AND ix.is_unique_constraint = 0 AND

/***********************************************************/

t.is_ms_shipped = 0

 AND t.name <> 'sysdiagrams'

 AND ix.name IS NOT NULL

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

/***********************************************************/

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,

@is_filtered,

@filter_definition,

@is_primary_key,

@is_unique_constraint

/*

--Commented out for older versions of SQL Server

,@compression_delay*/

WHILE (@@fetch_status = 0) 

BEGIN 

DECLARE @IndexColumns varchar(MAX); 

DECLARE @IncludedColumns varchar(MAX);

SET @IndexColumns='';

SET @IncludedColumns=''; 

SET @DataCompressionType = '';

 

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 

/*****************Ignores PK indexes************************/

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

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

    /***********************************************************/

ORDER BY ixc.key_ordinal

 

OPEN CursorIndexColumn 

FETCH NEXT

FROM CursorIndexColumn 

INTO @ColumnName,

        @IsDescendingKey,

        @IsIncludedColumn

   WHILE (@@fetch_status=0) 

BEGIN 

IF @IsIncludedColumn = 0 

OR @IndexTypeDesc LIKE '%COLUMNSTORE%' OR @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial' 

OR @IndexTypeDesc = 'NONCLUSTERED HASH'

SET @IndexColumns =

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' 

THEN @IndexColumns + '[' + @ColumnName + '], '

WHEN @IndexTypeDesc = 'XML' OR @IndexTypeDesc = 'spatial' OR @IndexTypeDesc = 'NONCLUSTERED HASH'

THEN @IndexColumns + '[' + @ColumnName + '], '

ELSE @IndexColumns + '[' + @ColumnName + ']' +

CASE

WHEN @IsDescendingKey = 1 THEN ' DESC, '

ELSE ' ASC, '

               END 

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;

 

SET @TSQLScripCreationIndex = '';

SET @TSQLScripDisableIndex = '';

SET @DataCompressionType = SUBSTRING(@IndexOptions,CHARINDEX('DATA_COMPRESSION',@IndexOptions)+18,LEN(@IndexOptions));

SET @TSQLScripCreationIndex =

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' THEN

CASE 

WHEN @IndexTypeDesc LIKE 'CLUSTERED%' THEN 

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) +

'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +  

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType))  + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';'

WHEN @is_filtered = 1 THEN

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

'' + @IndexColumns + CHAR(13) + ') ' + + CHAR(13) +

'WHERE ' + @filter_definition + CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' + 

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType))  + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';'

ELSE

'CREATE ' + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

'' + @IndexColumns + CHAR(13) + ') ' + 

CHAR(13) + 'WITH (COMPRESSION_DELAY = ' + CONVERT(varchar,@compression_delay) + ', DATA_COMPRESSION = ' +  

--SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType))  + ')

@DataCompressionType + ') ON ' + @FileGroupName + ';' END

/*

--Commented out for older versions of SQL Server

 

WHEN @IndexTypeDesc = 'MEMORY_OPTIMIZED' THEN

CASE WHEN @is_primary_key = 1 THEN 

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + ' PRIMARY KEY NONCLUSTERED'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'

WHEN @is_primary_key = 0 THEN

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @is_unique + ' NONCLUSTERED'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ');'END

WHEN @IndexTypeDesc = 'NONCLUSTERED HASH' THEN 

CASE WHEN @is_primary_key = 1 THEN 

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY NONCLUSTERED HASH'

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name =  @IndexName)

WHEN @is_primary_key = 0 THEN 

(SELECT 'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD INDEX ' + QUOTENAME(@IndexName) + ' ' + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' + 'WITH ( BUCKET_COUNT = ' + CONVERT(varchar,bucket_count) + ');'

       FROM sys.hash_indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name =  @IndexName) END

*/

WHEN @IndexTypeDesc = 'XML' THEN

CASE WHEN EXISTS (SELECT name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML' 

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN 

'CREATE PRIMARY XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) +

'' + @IndexColumns + CHAR(13) + ') ' +  CHAR(13) + 'WITH (' + @IndexOptions+ ');'

WHEN EXISTS (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description <> 'PRIMARY_XML' 

AND name = @IndexName AND object_id = OBJECT_ID(@SchemaName + '.' + @TableName)) THEN

(SELECT 'CREATE XML INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

'' + @IndexColumns + CHAR(13) + ')' + CHAR(13) + 'USING XML INDEX ' + QUOTENAME(P.name) 

+ ' FOR ' + I.secondary_type_desc collate latin1_general_cs_as  + ' WITH (' + @IndexOptions+ ');'

FROM sys.xml_indexes I

INNER JOIN (SELECT object_id, index_id, name FROM sys.xml_indexes WHERE xml_index_type_description = 'PRIMARY_XML') P 

ON P.object_id = I.object_id AND P.index_id = I.using_xml_index_id AND I.name = @IndexName) 

END

WHEN @IndexTypeDesc = 'spatial' THEN 

(SELECT 'CREATE SPATIAL INDEX ' + QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

'' + @IndexColumns + CHAR(13) + ')' + 'USING ' + tessellation_scheme + CHAR(13) 

+ 'WITH (BOUNDING_BOX =(' + CONVERT(varchar,bounding_box_xmin) + ', ' + CONVERT(varchar,bounding_box_ymin) + ', ' 

+ CONVERT(varchar,bounding_box_xmax) + ', ' + CONVERT(varchar,bounding_box_ymax)

+ '), GRIDS =(LEVEL1 = ' + level_1_grid_desc + ',LEVEL_2 = ' + level_2_grid_desc + ',LEVEL3 = ' + 

level_3_grid_desc + ',LEVEL4 = ' + level_4_grid_desc + '),

CELLS_PER_OBJECT = ' + CONVERT(varchar,cells_per_object) + ', ' + @IndexOptions + ') ON ' + @FileGroupName + ';' 

FROM sys.spatial_index_tessellations

WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND index_id = 

(SELECT index_id from sys.indexes WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName) AND name =  @IndexName))

WHEN @is_filtered = 1 THEN

'CREATE ' + @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) ++ '(' + CHAR(13) + 

'' + @IndexColumns + CHAR(13) + ') ' +  

CASE

                                   WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

                                   ELSE ''

                               END + 

CHAR(13) + 'WHERE ' + @filter_definition + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

WHEN @is_primary_key = 1 THEN 

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' PRIMARY KEY ' + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) + 

'' + @IndexColumns + CHAR(13) + ') ' + CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';'

WHEN @is_unique_constraint = 1 THEN 

'ALTER TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ADD  CONSTRAINT ' + QUOTENAME(@IndexName) + ' ' + @is_unique + @IndexTypeDesc

+ CHAR(13) + '(' + CHAR(13) + '' + @IndexColumns + CHAR(13) + ') ' +  CHAR(13) + 'WITH (' + @IndexOptions+ ');'

ELSE

'CREATE '+ @is_unique + @IndexTypeDesc + ' INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + CHAR(13) + '(' + CHAR(13) + 

'' + @IndexColumns + CHAR(13) + ') ' +  

CASE

                                   WHEN len(@IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @IncludedColumns+ ')'

                                   ELSE ''

                               END + 

CHAR(13) + 'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';' 

   END;

IF @is_disabled = 1

SET @TSQLScripDisableIndex = CHAR(13) +'ALTER INDEX ' + QUOTENAME(@IndexName) + ' ON ' + 

QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) + 'GO' + + CHAR(13) 

 

PRINT @TSQLScripCreationIndex;

PRINT 'GO'; 

PRINT @TSQLScripDisableIndex;

 

FETCH NEXT

FROM CursorIndex 

INTO @SchemaName,

    @TableName,

    @IndexName,

    @is_unique,

    @IndexTypeDesc,

    @IndexOptions,

    @is_disabled,

    @FileGroupName,

@is_filtered,

@filter_definition,

@is_primary_key,

@is_unique_constraint

/*

--Commented out for older versions of SQL Server

,@compression_delay */

END;

CLOSE CursorIndex;

DEALLOCATE CursorIndex;


Thursday, April 28, 2016 - 3:35:51 PM - Pete Danes Back To Top (41365)

Neat, but it doesn't handle filter clauses on indexes. Still, it helped me assemble the script I needed for actually deleting and recreating all the indexes on a table. I needed to change the collation on a field and there were a bunch of indexes on that one field. Appreciate you posting this.


Thursday, April 28, 2016 - 7:12:59 AM - Chris Ulrich Back To Top (41359)

This was SO incredibly helpful.  We were doing bulk updates on 20 tables, dropping about 500,000 records daily, adding 500,000 daily- to each table.   TO create all DROP & CREATE statements like this - on 20 tables with about 15 indexes each - an unbelievable time saver.

Thank you!


Thursday, April 14, 2016 - 5:05:02 PM - GaryS Back To Top (41233)

Great code, thank you for writing this! I have adjusted the script "T-SQL Script to Create All SQL Server Indexes" to include ALL indexes (clustered, nonclustered, PK (not with an alter table statement but with a create unique statement), partitioned, and columnstore indexes). I have also added data compression, when applicable. Hope this helps!

 

--T-SQL Script to Create All SQL Server Indexes, written by Percy Reyes, modified by GaryS 4/14/2016 

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 @DataCompressionType varchar(100) 

DECLARE @IndexColumnId int 

DECLARE @IsDescendingKey int 

DECLARE @IsIncludedColumn int 

DECLARE @TSQLScripCreationIndex varchar(MAX) 

DECLARE @TSQLScripDisableIndex varchar(MAX) 

 

DECLARE CursorIndex

 CURSOR

 FOR 

--CTE to collect partitioned index information

WITH PartitionedIndexes AS ( 

SELECT t.object_id Object_ID,

          t.name TableName,

          ic.column_id PartitioningColumnID,

          c.name PartitioningColumnName,

          s.name AS [partition_scheme],

          ix.name AS IndexName,

          ix.index_id

  FROM sys.tables t

  INNER JOIN sys.indexes i ON i.object_id = t.object_id

  INNER JOIN sys.index_columns ic ON ic.index_id = i.index_id

  AND ic.object_id = t.object_id

  INNER JOIN sys.columns c ON c.object_id = ic.object_id

  AND c.column_id = ic.column_id

  INNER JOIN sys.partition_schemes s ON i.data_space_id = s.data_space_id

  INNER JOIN sys.indexes ix ON t.object_id=ix.object_id

  AND ix.index_id = i.index_id

  WHERE --t.object_id  = object_id(@TableName)AND

ic.partition_ordinal = 1

)

 

SELECT schema_name(t.schema_id) [schema_name],

      t.name TableName,

      ix.name IndexName,

      CASE

          WHEN ix.is_unique = 1 THEN 'UNIQUE '

          ELSE ''

      END IsUnique ,

      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, ' + 

  CASE

          WHEN CAST(ix.fill_factor AS VARCHAR(3)) = 0 THEN ''

          ELSE 'FILLFACTOR =' + CAST(ix.fill_factor AS VARCHAR(3)) + ', '

      END + 'DATA_COMPRESSION =' + p.data_compression_desc +

  CASE

  WHEN (@@Version LIKE '%ENTERPRISE%' AND (LOBTable.CanBeBuiltOnline IS NULL AND ix.type_desc = 'CLUSTERED'))

OR (@@Version LIKE '%ENTERPRISE%' AND (LOBIndexes.CanBeBuiltOnline IS NULL AND ix.type_desc <> 'CLUSTERED')) THEN ', ONLINE = ON'

  ELSE '' END

  AS IndexOptions 

  ,

      ix.is_disabled,

      CASE

          WHEN ic.IsColumnPartitioned = 1 THEN '[' + PIdx.[partition_scheme] + ']' + '(' + '[' + PIdx.PartitioningColumnName + ']' + ')'

          WHEN ic.IsColumnPartitioned = 0 THEN '[' + FILEGROUP_NAME(ix.data_space_id) + ']'

      END AS FileGroupName

FROM sys.tables t

INNER JOIN sys.indexes ix ON t.object_id=ix.object_id

INNER JOIN

 (SELECT DISTINCT OBJECT_ID,

                  index_id,

                  MAX(partition_ordinal) AS IsColumnPartitioned

  FROM sys.index_columns

  GROUP BY OBJECT_ID,

           index_id) ic ON ic.index_id = ix.index_id

AND ic.object_id = t.object_id

INNER JOIN

 (SELECT DISTINCT object_id,

                  index_id,

                  data_compression_desc

  FROM sys.partitions) p ON ix.object_id = p.object_id

AND ix.index_id = p.index_id

LEFT JOIN PartitionedIndexes PIdx ON PIdx.Object_ID = t.object_id

AND PIdx.index_id = ix.index_id

LEFT JOIN 

(SELECT DISTINCT object_id,

                0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

WHERE t.Name IN ('image',

                'ntext',

                'text',

                'XML')

 OR (t.Name IN ('varchar',

                'nvarchar',

                'varbinary')

     AND c.max_length = -1)

 OR c.is_filestream = 1) LOBTable ON LOBTable.object_id = t.object_id

LEFT JOIN 

(SELECT DISTINCT c.object_id,

            i.index_id,

            0 AS CanBeBuiltOnline

FROM sys.columns c

INNER JOIN sys.types t ON c.user_type_id = t.user_type_id

LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id

AND ic.column_id = c.column_id

INNER JOIN sys.indexes i ON ic.object_id = i.object_id

AND ic.index_id = i.index_id

WHERE t.Name IN ('image',

                'ntext',

                'text',

                'XML')

 OR (t.Name IN ('varchar',

                'nvarchar',

                'varbinary')

     AND c.max_length = -1)

 OR c.is_filestream = 1) LOBIndexes ON LOBIndexes.object_id = t.object_id 

AND LOBIndexes.index_id = ix.index_id

WHERE  

/*****************Ignores PK indexes************************/

--ix.type>0 AND ix.is_primary_key=0 AND ix.is_unique_constraint=0 AND

/***********************************************************/

t.is_ms_shipped=0

 AND t.name<>'sysdiagrams'

 AND ix.name IS NOT NULL

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

/***********************************************************/

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='' 

SET @DataCompressionType = ''

 

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 

/*****************Ignores PK indexes************************/

--ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0) AND

/***********************************************************/

 schema_name(tb.schema_id)[email protected]

 AND [email protected]

 AND [email protected]

/*****************Ignores clustered indexes*****************/

--AND ix.type_desc <> 'CLUSTERED'

/***********************************************************/

/*****************Ignores COLUMNSTORE indexes***************/

--AND ix.type_desc NOT LIKE '%COLUMNSTORE%'

    /***********************************************************/

ORDER BY ixc.index_column_id 

 

OPEN CursorIndexColumn 

FETCH NEXT

FROM CursorIndexColumn 

INTO @ColumnName,

        @IsDescendingKey,

        @IsIncludedColumn

   WHILE (@@fetch_status=0) 

BEGIN 

IF @IsIncludedColumn=0 OR @IndexTypeDesc LIKE '%COLUMNSTORE%'

SET @IndexColumns=

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' THEN @IndexColumns + '[' + @ColumnName + '], '

ELSE @IndexColumns + '[' + @ColumnName + ']' +

CASE

WHEN @IsDescendingKey=1 THEN ' DESC, '

ELSE ' ASC, '

               END 

END

ELSE

SET @[email protected] + '[' + @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

 

SET @TSQLScripCreationIndex =''

SET @TSQLScripDisableIndex =''

SET @DataCompressionType = SUBSTRING(@IndexOptions,CHARINDEX('DATA_COMPRESSION',@IndexOptions)+18,LEN(@IndexOptions))

SET @TSQLScripCreationIndex=

CASE WHEN @IndexTypeDesc LIKE '%COLUMNSTORE%' THEN

'CREATE '+ @IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + 

QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ('[email protected]+') '+ 

CHAR(13)+'WITH (DATA_COMPRESSION = ' + SUBSTRING(@DataCompressionType,0,CHARINDEX(',',@DataCompressionType))  + ');'

ELSE

 

'CREATE '+ @is_unique [email protected] + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + 

QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ('[email protected]+') '+ 

CASE

                                   WHEN len(@IncludedColumns)>0 THEN CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')'

                                   ELSE ''

                               END + 

CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + @FileGroupName + ';' 

END

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


Tuesday, January 26, 2016 - 3:31:37 PM - Verena_Techie Back To Top (40506)

Your scripts require 2 changes:

Create:

There is no space after the table name and before the index columns:

QUOTENAME(@TableName)+ '('[email protected]+') '+

becomes:

QUOTENAME(@TableName)+ ' ('[email protected]+') '+

Drop:

Syntax is wrong:

SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@SchemaName)+ '.' + QUOTENAME(@TableName) + '.' +QUOTENAME(@IndexName)

becomes:

SET @TSQLDropIndex = 'DROP INDEX '+QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)

Other than that it's a useful script, thanks!


Thursday, January 21, 2016 - 12:30:33 AM - Roustam Back To Top (40463)

 Thanks for the script, however, what should I do if I want to include the partitioned and unique indexes (including the PKs)?

 

.


Wednesday, December 16, 2015 - 2:09:09 PM - Scott W Back To Top (40254)

Huge timesaver, Percy! Thank you. I did run into the same issue that Gene did with the columns not in the right order, so I run the query below to spot check ones that might be off. This was on Microsoft SQL Server 2008 R2 (SP3) - 10.50.6000.34.  Mine was actually a PK, and I was using your script from here. Thought I would post here so others were aware. Seems like a rare thing to happen, but better to be safe than sorry.

select object_name(ic.object_id) "table_name", ic.Index_id, i.name, ic.index_column_id, ic.key_ordinal, ic.is_included_column
from sys.index_columns ic
inner join sys.indexes i on i.object_id = ic.object_id and i.index_id = ic.index_id
where ic.index_column_id <> ic.key_ordinal
  and object_name(ic.object_id) not like 'sys%'
  and object_name(ic.object_id) not like 'queue%'
order by ic.is_included_column, "table_name", ic.Index_id, ic.index_column_id

 


Tuesday, September 22, 2015 - 5:13:39 AM - Cristi Boboc Back To Top (38722)

I have refactored the code above to avoid variables and cursors. Kindly please see below the pure SQL version:


SELECT 'CREATE ' +
         CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END +
         (i.type_desc COLLATE SQL_Latin1_General_CP1_CI_AS) +
         ' INDEX ' +
         QUOTENAME(i.[name]) +
         ' ON ' +
         QUOTENAME(schema_name(t.schema_id)) +
         '.' +
         QUOTENAME(t.[name]) +
         REPLACE(REPLACE(REPLACE((SELECT QUOTENAME(col_name(object_id, column_id)) + CASE WHEN c.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END x
                                    FROM [sys].[index_columns] c
                                    WHERE c.[object_id] = i.[object_id] AND
                                          c.[index_id]  = i.[index_id]  AND
                                          c.[is_included_column] = 0
                                    ORDER BY c.[index_column_id]
                                    FOR XML PATH('')), '', ', '), '', ')'), '', '(') +
         COALESCE(' INCLUDE ' + REPLACE(REPLACE(REPLACE((SELECT QUOTENAME(col_name(object_id, column_id)) + CASE WHEN c.[is_descending_key] = 1 THEN ' DESC' ELSE ' ASC' END x
                                                           FROM [sys].[index_columns] c
                                                           WHERE c.[object_id] = i.[object_id] AND
                                                                 c.[index_id]  = i.[index_id]  AND
                                                                 c.[is_included_column] = 1
                                                           ORDER BY c.[index_column_id]
                                                           FOR XML PATH('')), '', ', '), '', ')'), '', '('), '') +
         ' WITH (' + CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END +
         CASE WHEN i.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END +
         CASE WHEN i.[allow_row_locks]  = 1 THEN 'ALLOW_ROW_LOCKS = ON,  ' ELSE 'ALLOW_ROW_LOCKS = OFF,  ' END +
         CASE WHEN INDEXPROPERTY(t.object_id, i.[name], 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END +
         CASE WHEN i.[ignore_dup_key]   = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END +
         'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(i.fill_factor AS VARCHAR(3)) + ') ON ' +
         QUOTENAME(FILEGROUP_NAME(i.data_space_id)) +
         ';',
       schema_name(t.schema_id) [schema_name],
       t.[name],
       i.[name],
       CASE WHEN i.is_unique = 1 THEN 'UNIQUE ' ELSE '' END,
       i.type_desc,
       CASE WHEN i.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END +
         CASE WHEN i.[allow_page_locks] = 1 THEN 'ALLOW_PAGE_LOCKS = ON, ' ELSE 'ALLOW_PAGE_LOCKS = OFF, ' END +
         CASE WHEN i.[allow_row_locks]  = 1 THEN 'ALLOW_ROW_LOCKS = ON,  ' ELSE 'ALLOW_ROW_LOCKS = OFF,  ' END +
         CASE WHEN INDEXPROPERTY(t.object_id, i.[name], 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END +
         CASE WHEN i.[ignore_dup_key]   = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END +
         'SORT_IN_TEMPDB = OFF, FILLFACTOR =' + CAST(i.fill_factor AS VARCHAR(3)) AS IndexOptions,
         i.is_disabled,
         FILEGROUP_NAME(i.data_space_id) FileGroupName
  FROM [sys].[tables]  t JOIN
       [sys].[indexes] i ON t.object_id = i.object_id
  WHERE i.[type] > 0 AND i.is_primary_key = 0 AND i.is_unique_constraint = 0 --AND schema_name(t.schema_id)= @SchemaName AND [email protected] AND t.is_ms_shipped=0 AND t.name<>'sysdiagrams'
  ORDER BY schema_name(t.schema_id),
           t.[name],
           i.[name]

 


Friday, August 21, 2015 - 4:28:02 PM - Gene Back To Top (38509)

This is saving me a ton of work and I really appreciate you sharing it.

I found a problem with the sequence of the columns for an index or ours. This is probably something that rarely happens, but it changed the way the index would be built. The index columns for it had a difference between the values in its sys.index_columns rows between the index_column_id and key_ordinal columns (see example below). When I right click on the Index in SSMS and Script to a new query window the columns appear in the order that matches the key_ordinal column not the index_column_id column. So I changed the order by clause in your script to replace order by ixc.index_column_id with order by ixc.key_ordinal and then it matched the way the SSMS script to would script it out.

Try it out with this minor change to verify the results I saw:

--order by ixc.index_column_id 

   order by ixc.key_ordinal

index_column_id key_ordinal

1               3

2               4

3               1

4               2

 

Monday, August 3, 2015 - 8:20:07 AM - Scott Back To Top (38350)

This doesn't correctly handle columnstore indexes (SQL 2012) - puts the field list in the INCLUDE clause


Monday, January 5, 2015 - 4:37:14 PM - Greg Robidoux Back To Top (35833)

Percy, congrats on becoming a SQL Server MVP: http://mvp.microsoft.com/en-us/mvp/Percy%20Reyes-5001252

-Greg Robidoux


Monday, December 29, 2014 - 1:22:41 PM - Jim Lastman Back To Top (35782)

This is a good set of SQL to add to the library of utility scripts.  Do you have any suggestions on how to include the extra bit for filtered indexes?















get free sql tips
agree to terms