Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

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


By:   |   Read Comments (20)   |   Related Tips: More > Indexing

Attend these FREE MSSQLTips webcasts >> click to register


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 tb.name=@TableName
 and t.is_ms_shipped=0 and t.name<>'sysdiagrams'
 order by schema_name(t.schema_id), t.name, ix.name

open CursorIndex
fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

while (@@fetch_status=0)
begin
 declare @IndexColumns varchar(max)
 declare @IncludedColumns varchar(max)
 
 set @IndexColumns=''
 set @IncludedColumns=''
 
 declare CursorIndexColumn cursor for 
  select col.name, ixc.is_descending_key, ixc.is_included_column
  from sys.tables tb 
  inner join sys.indexes ix on tb.object_id=ix.object_id
  inner join sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join sys.columns col on ixc.object_id =col.object_id  and ixc.column_id=col.column_id
  where ix.type>0 and (ix.is_primary_key=0 or ix.is_unique_constraint=0)
  and schema_name(tb.schema_id)=@SchemaName and tb.name=@TableName and ix.name=@IndexName
  order by ixc.index_column_id
 
 open CursorIndexColumn 
 fetch next from CursorIndexColumn into  @ColumnName, @IsDescendingKey, @IsIncludedColumn
 
 while (@@fetch_status=0)
 begin
  if @IsIncludedColumn=0 
   set @IndexColumns=@IndexColumns + @ColumnName  + case when @IsDescendingKey=1  then ' DESC, ' else  ' ASC, ' end
  else 
   set @IncludedColumns=@IncludedColumns  + @ColumnName  +', ' 

  fetch next from CursorIndexColumn into @ColumnName, @IsDescendingKey, @IsIncludedColumn
 end

 close CursorIndexColumn
 deallocate CursorIndexColumn

 set @IndexColumns = substring(@IndexColumns, 1, len(@IndexColumns)-1)
 set @IncludedColumns = case when len(@IncludedColumns) >0 then substring(@IncludedColumns, 1, len(@IncludedColumns)-1) else '' end
 --  print @IndexColumns
 --  print @IncludedColumns

 set @TSQLScripCreationIndex =''
 set @TSQLScripDisableIndex =''
 set @TSQLScripCreationIndex='CREATE '+ @is_unique  +@IndexTypeDesc + ' INDEX ' +QUOTENAME(@IndexName)+' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ '('+@IndexColumns+') '+ 
  case when len(@IncludedColumns)>0 then CHAR(13) +'INCLUDE (' + @IncludedColumns+ ')' else '' end + CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + QUOTENAME(@FileGroupName) + ';'  

 if @is_disabled=1 
  set  @TSQLScripDisableIndex=  CHAR(13) +'ALTER INDEX ' +QUOTENAME(@IndexName) + ' ON ' + QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName) + ' DISABLE;' + CHAR(13) 

 print @TSQLScripCreationIndex
 print @TSQLScripDisableIndex

 fetch next from CursorIndex into  @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions,@is_disabled, @FileGroupName

end
close CursorIndex
deallocate CursorIndex

 

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:


Last Update:


signup button

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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Wednesday, August 02, 2017 - 9:12:53 AM - jschenck Back To Top

 

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

 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 t.name=@TableName 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 t.name=@TableName 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

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 06, 2017 - 4:20:30 PM - JP Back To Top

 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 09, 2016 - 9:30:31 AM - Wendel Back To Top

 Hi Percy, Very Very, Thank You.

 

 


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

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

 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

 Tnaks Percy. This saved me a lot of time.

 


Friday, July 08, 2016 - 11:48:41 AM - GaryS Back To Top

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

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

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

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)=@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.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 @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

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

QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ('+@IndexColumns+') '+ 

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

ELSE

 

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

QUOTENAME(@SchemaName) +'.'+ QUOTENAME(@TableName)+ ' ('+@IndexColumns+') '+ 

CASE

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

                                   ELSE ''

                               END + 

CHAR(13)+'WITH (' + @IndexOptions+ ') ON ' + @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

Your scripts require 2 changes:

Create:

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

QUOTENAME(@TableName)+ '('+@IndexColumns+') '+

becomes:

QUOTENAME(@TableName)+ ' ('+@IndexColumns+') '+

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

 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

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

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 t.name=@TableName 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

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 03, 2015 - 8:20:07 AM - Scott Back To Top

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


Monday, January 05, 2015 - 4:37:14 PM - Greg Robidoux Back To Top

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

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?


Learn more about SQL Server tools