CODEWITHSUNDEEP
sql-servert-sqlindexing
Thakur
Thakur

Reputation: 2020

Generate script of All the indexes in a database

I want to generate a script of all the indexes in a database(Create Index).

I don't want the index script along with create table script. how can we generate it in SQL Server (2005 or 2008). There needs to be separate script for Clustered and Non Clustered Indexes.

Upvotes: 52

Views: 134060

Answers (8)

Ehsan R
Ehsan R

Reputation: 91

Howard Rothenburg has a Good Answer. but If an Index has some kind of special characters like & ,... it causes error. It would be better to replace the 7th Line from:

  I.name + ' ON ' +

to:

'[' + I.name+ ']'   + ' ON ' +

which by replacing to it, will be OK.

and the Final Code to Generate Create script of All the indexes in a database would be like this:

SELECT ' CREATE ' +
       CASE 
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       '[' + I.name+ ']'   + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE 
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE 
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value 
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE 
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE 
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE 
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE 
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE 
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                            = 1 THEN 
                                                                            ' DESC '
                                                                       ELSE 
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal) 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           --AND I.Object_id = object_id('Person.Address') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Upvotes: 0

Martin
Martin

Reputation: 131

Current versions of SQL Server Management Studio have an option to include indexes in generated scripts. Right click on database name, choose Tasks, Generate Scripts... Follow the dialogue and in advanced options change "Script Indexes" to true. Seems to remember the setting for future use.

Upvotes: 13

James Mc
James Mc

Reputation: 597

Here's a more concise version updated for SQL Server 2017 and above, and now includes indexes on views:

-- remove sys.tables or sys.views from this CTE as needed
with base_objects as
(   Select Name, object_ID, schema_ID, type_desc
    from sys.tables
    union all
    Select  Name, object_ID, schema_ID, type_desc
    from sys.views
)
  
SELECT
    [Table_name] 
,   tbl.[type_desc]
,   [Table_object_ID] 
,   [Index_name] 
,   idx.Index_ID
,   [Definition] = 'CREATE ' + [unique] + idx.[type_desc] + ' INDEX [' + index_name + '] ON ' 
            + SCHEMA_NAME(schema_id) + '.' + Table_name 
            + ' ( ' +  key_cols + ' )'
            + isnull(' INCLUDE ( ' + inc_cols + ' ) ','')
            + ' WITH (' + [options] + ' )'
            + ' ON [' + dat.name + ']' +  CHAR(13) + CHAR(10) + ' GO'
FROM Sys.Indexes idx
    join base_objects tbl
        on tbl.object_id = idx.object_ID
    join sys.stats stat
        ON  stat.object_id = idx.object_id
        AND stat.stats_id = idx.index_id
    JOIN sys.data_spaces dat
        ON  idx.data_space_id = dat.data_space_id
    cross apply (Select
        [Table_name] = OBJECT_NAME(idx.Object_ID)
    ,   [Table_object_ID] = idx.Object_ID
    ,   [Index_name] = idx.Name
    ,   [unique] = case when is_unique = 1 then 'UNIQUE ' else '' end

    ) labels
    cross apply (Select
        key_cols = string_agg(key_col_name, ', ') collate DATABASE_DEFAULT
    ,   inc_cols = string_agg(inc_col_name, ', ') collate DATABASE_DEFAULT
        from
            sys.index_columns sub_ic
            join sys.columns sub_col
                on sub_col.object_ID = sub_ic.object_id and sub_col.column_id = sub_ic.column_id
            cross apply (Select
                key_col_name = case when is_included_column = 0 then sub_col.name end
            ,   inc_col_name = case when is_included_column = 1 then sub_col.name end
            ) key_inc
        where sub_ic.object_id = idx.object_id and sub_ic.index_id = idx.index_id
            and is_included_column = 0
    ) cols
    cross apply (Select
        options = string_agg([option] + on_off, ', ')
        from (values
          ( 'PAD_INDEX = ' , idx.is_padded)
        , ( 'FILLFACTOR = ', nullif(idx.fill_factor, 0))
        , ( 'IGNORE_DUP_KEY = ', idx.ignore_dup_key)
        , ( 'STATISTICS_NORECOMPUTE = ', stat.no_recompute)
        , ( 'ALLOW_ROW_LOCKS = ', idx.allow_row_locks)
        , ( 'ALLOW_PAGE_LOCKS = ', idx.allow_page_locks)
        ) opts([option], val)
        cross apply (Select
            on_off = case val when 1 then 'ON' when 0 then 'OFF' else CONVERT( CHAR(5), val) end
        ) on_off_calc
    ) options_calc

where idx.name is not null

Upvotes: 5

Colleen Triglianos
Colleen Triglianos

Reputation: 1

I know this thread is super old and this isn't the prettiest thing in the world, but I needed a stored proc that would generated the indexes for a specified database and couldn't find that on the internets so this is what I created.

This stored procedure takes a database name as a parameter and then produces the tsql to create all the indexes in the database. It takes into account index options, schema, owner and INCLUDE columns. 

```
use dbatools
go

create procedure usp_script_index @dbname sysname
as

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)

 IF DB_ID(@dbname) IS NULL  /*Validate the database name exists*/
       BEGIN
       RAISERROR('Invalid Database Name passed',16,1)
       RETURN
       END

set nocount on 

create table #tbls (
sch_name sysname,
tbl_name sysname,
index_name sysname,
unique_flag varchar(20),
type_desc varchar(20),
indexoptions varchar(500),
is_disabled int,
fileGroupName sysname
)

DECLARE @dynsql nvarchar(max)  
declare @dynsql2 nvarchar(max)

 /*Use QUOTENAME to correctly escape any special characters*/
SET @dynsql = N'insert #tbls select 
schema_name(t.schema_id) [sch_name], 
t.name as tbl_name, 
ix.name as index_name,
 case when ix.is_unique = 1 then ''UNIQUE '' else '''' END as unique_flag
 , 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 ' + @dbname +'.sys.tables t 
 inner join ' + @dbname +'.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 t.is_ms_shipped=0 and t.name<>''sysdiagrams''
 order by schema_name(t.schema_id), t.name, ix.name'

 exec sp_executesql @dynsql

print 'use '+ @dbname + char(10) +'go' +char(10) 

declare CursorIndex cursor for select sch_name, tbl_name, index_name,unique_flag, type_desc, indexoptions,is_disabled,fileGroupName from #tbls

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

 create table #cols
(
    column_name sysname,
    is_descending_key int,
    is_included_column int
)

 SET @dynsql2 = N' insert #cols
  select col.name as column_name, ixc.is_descending_key, ixc.is_included_column
  from '+ @dbname + '.sys.tables tb 
  inner join ' +@dbname +'.sys.indexes ix on tb.object_id=ix.object_id
  inner join ' +@dbname +'.sys.index_columns ixc on ix.object_id=ixc.object_id and ix.index_id= ixc.index_id
  inner join ' +@dbname +'.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 '

 --print @dynsql2
 exec sp_executesql @dynsql2

 declare CursorIndexColumn cursor for select column_name,is_descending_key,is_included_column from #cols

 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

 drop table #cols
 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
drop table #tbls

go
```

Upvotes: 0

Gus
Gus

Reputation: 91

I use the following, which includes the schemas and IF NOT EXISTS, to generate the postdeploymentscripts I need for my database projects in visual studio:

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
   SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID, SC.name
      FROM Sys.Indexes SI 
         LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
         LEFT JOIN SYS.tables TB ON (OBJECT_NAME(SI.Object_ID) = TB.name)
         LEFT JOIN SYS.schemas SC ON (TB.schema_id=SC.schema_id)
      WHERE TC.CONSTRAINT_NAME IS NULL
         AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
      ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT
DECLARE @IxSchema SYSNAME
DECLARE @PKSQL VARCHAR(50)

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @IxSchema
WHILE (@@FETCH_STATUS = 0)
BEGIN
   DECLARE @IXSQL NVARCHAR(4000) 
   SET @PKSQL = ''
   SET @IXSQL = 'IF NOT EXISTS(SELECT TOP 1 1 FROM sys.indexes WHERE name=''' + @IxName + ''' AND object_id = OBJECT_ID(''' + @IxSchema + '.' + @IxTable + '''))'
   SET @IXSQL = @IXSQL + CHAR(13) + CHAR(10)
   SET @IXSQL = @IXSQL + 'BEGIN'
   SET @IXSQL = @IXSQL + CHAR(13) + CHAR(10)
   SET @IXSQL = @IXSQL + CHAR(9) + 'CREATE '

   -- Check if the index is unique
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
      SET @IXSQL = @IXSQL + 'UNIQUE '
   -- Check if the index is clustered
   IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
      SET @IXSQL = @IXSQL + 'CLUSTERED '

   SET @IXSQL = @IXSQL + 'INDEX [' + @IxName + '] ON [' + @IxSchema + '].[' + @IxTable + ']('

   -- Get all columns of the index
   DECLARE cIxColumn CURSOR FOR 
      SELECT SC.Name
      FROM Sys.Index_Columns IC
         JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
      WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
      ORDER BY IC.Index_Column_ID

   DECLARE @IxColumn SYSNAME
   DECLARE @IxFirstColumn BIT, @ColumnCount INT 
   SET @IxFirstColumn = 1
   SET @ColumnCount = 0

   -- Loop throug all columns of the index and append them to the CREATE statement
   OPEN cIxColumn
   FETCH NEXT FROM cIxColumn INTO @IxColumn
   WHILE (@@FETCH_STATUS = 0)
   BEGIN
      IF (@ColumnCount < 16)
      BEGIN
          IF (@IxFirstColumn = 1)
             SET @IxFirstColumn = 0
          ELSE
             SET @IXSQL = @IXSQL + ', '

          SET @IXSQL = @IXSQL + '[' + @IxColumn + ']'
          SET @ColumnCount = @ColumnCount + 1
      END
      FETCH NEXT FROM cIxColumn INTO @IxColumn
   END
   CLOSE cIxColumn
   DEALLOCATE cIxColumn

   SET @IXSQL = @IXSQL + ')'

   SET @IXSQL = @IXSQL + CHAR(13) + CHAR(10)
   SET @IXSQL = @IXSQL + 'END'
   -- Print out the CREATE statement for the index
   IF(LEN(@IXSQL) > 10)
    PRINT @IXSQL

   FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID, @IxSchema
END

CLOSE cIX
DEALLOCATE cIX

Upvotes: 0

Tim Lehner
Tim Lehner

Reputation: 15251

This is an excellent article on SQL Server Central that I've used.

Here is another that you might like better.

If it's not exactly what you're looking for (sorting, filtering) I'm sure it would be easier to edit these than to start from scratch.

Here's the slightly corrected (2019) code from the first example:

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
    SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
        FROM Sys.Indexes SI 
            LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
        WHERE TC.CONSTRAINT_NAME IS NULL
            AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
        ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
    DECLARE @IXSQL NVARCHAR(4000) SET @IXSQL = ''
    SET @IXSQL = 'CREATE '

    -- Check if the index is unique
    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
        SET @IXSQL = @IXSQL + 'UNIQUE '
    -- Check if the index is clustered
    IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
        SET @IXSQL = @IXSQL + 'CLUSTERED '

    SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

    -- Get all columns of the index
    DECLARE cIxColumn CURSOR FOR 
        SELECT SC.Name
        FROM Sys.Index_Columns IC
            JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
        WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
        ORDER BY IC.Index_Column_ID

    DECLARE @IxColumn SYSNAME
    DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

    -- Loop throug all columns of the index and append them to the CREATE statement
    OPEN cIxColumn
    FETCH NEXT FROM cIxColumn INTO @IxColumn
    WHILE (@@FETCH_STATUS = 0)
    BEGIN
        IF (@IxFirstColumn = 1)
            SET @IxFirstColumn = 0
        ELSE
            SET @IXSQL = @IXSQL + ', '

        SET @IXSQL = @IXSQL + @IxColumn

        FETCH NEXT FROM cIxColumn INTO @IxColumn
    END
    CLOSE cIxColumn
    DEALLOCATE cIxColumn

    SET @IXSQL = @IXSQL + ')'
    -- Print out the CREATE statement for the index
    IF @IXSQL != '' BEGIN PRINT @IXSQL END

    FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

Upvotes: 31

Howard Rothenburg
Howard Rothenburg

Reputation: 1348

SELECT ' CREATE ' +
       CASE 
            WHEN I.is_unique = 1 THEN ' UNIQUE '
            ELSE ''
       END +
       I.type_desc COLLATE DATABASE_DEFAULT + ' INDEX ' +
       I.name + ' ON ' +
       SCHEMA_NAME(T.schema_id) + '.' + T.name + ' ( ' +
       KeyColumns + ' )  ' +
       ISNULL(' INCLUDE (' + IncludedColumns + ' ) ', '') +
       ISNULL(' WHERE  ' + I.filter_definition, '') + ' WITH ( ' +
       CASE 
            WHEN I.is_padded = 1 THEN ' PAD_INDEX = ON '
            ELSE ' PAD_INDEX = OFF '
       END + ',' +
       'FILLFACTOR = ' + CONVERT(
           CHAR(5),
           CASE 
                WHEN I.fill_factor = 0 THEN 100
                ELSE I.fill_factor
           END
       ) + ',' +
       -- default value 
       'SORT_IN_TEMPDB = OFF ' + ',' +
       CASE 
            WHEN I.ignore_dup_key = 1 THEN ' IGNORE_DUP_KEY = ON '
            ELSE ' IGNORE_DUP_KEY = OFF '
       END + ',' +
       CASE 
            WHEN ST.no_recompute = 0 THEN ' STATISTICS_NORECOMPUTE = OFF '
            ELSE ' STATISTICS_NORECOMPUTE = ON '
       END + ',' +
       ' ONLINE = OFF ' + ',' +
       CASE 
            WHEN I.allow_row_locks = 1 THEN ' ALLOW_ROW_LOCKS = ON '
            ELSE ' ALLOW_ROW_LOCKS = OFF '
       END + ',' +
       CASE 
            WHEN I.allow_page_locks = 1 THEN ' ALLOW_PAGE_LOCKS = ON '
            ELSE ' ALLOW_PAGE_LOCKS = OFF '
       END + ' ) ON [' +
       DS.name + ' ] ' +  CHAR(13) + CHAR(10) + ' GO' [CreateIndexScript]
FROM   sys.indexes I
       JOIN sys.tables T
            ON  T.object_id = I.object_id
       JOIN sys.sysindexes SI
            ON  I.object_id = SI.id
            AND I.index_id = SI.indid
       JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name + CASE 
                                                                       WHEN MAX(CONVERT(INT, IC1.is_descending_key)) 
                                                                            = 1 THEN 
                                                                            ' DESC '
                                                                       ELSE 
                                                                            ' ASC '
                                                                  END
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          0
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id
                                          ORDER BY
                                                 MAX(IC1.key_ordinal) 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) KeyColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp3
            )tmp4
            ON  I.object_id = tmp4.object_id
            AND I.Index_id = tmp4.index_id
       JOIN sys.stats ST
            ON  ST.object_id = I.object_id
            AND ST.stats_id = I.index_id
       JOIN sys.data_spaces DS
            ON  I.data_space_id = DS.data_space_id
       JOIN sys.filegroups FG
            ON  I.data_space_id = FG.data_space_id
       LEFT JOIN (
                SELECT *
                FROM   (
                           SELECT IC2.object_id,
                                  IC2.index_id,
                                  STUFF(
                                      (
                                          SELECT ' , ' + C.name
                                          FROM   sys.index_columns IC1
                                                 JOIN sys.columns C
                                                      ON  C.object_id = IC1.object_id
                                                      AND C.column_id = IC1.column_id
                                                      AND IC1.is_included_column = 
                                                          1
                                          WHERE  IC1.object_id = IC2.object_id
                                                 AND IC1.index_id = IC2.index_id
                                          GROUP BY
                                                 IC1.object_id,
                                                 C.name,
                                                 index_id 
                                                 FOR XML PATH('')
                                      ),
                                      1,
                                      2,
                                      ''
                                  ) IncludedColumns
                           FROM   sys.index_columns IC2 
                                  --WHERE IC2.Object_id = object_id('Person.Address') --Comment for all tables
                           GROUP BY
                                  IC2.object_id,
                                  IC2.index_id
                       ) tmp1
                WHERE  IncludedColumns IS NOT NULL
            ) tmp2
            ON  tmp2.object_id = I.object_id
            AND tmp2.index_id = I.index_id
WHERE  I.is_primary_key = 0
       AND I.is_unique_constraint = 0
           --AND I.Object_id = object_id('Person.Address') --Comment for all tables
           --AND I.name = 'IX_Address_PostalCode' --comment for all indexes

Upvotes: 77

Estevez
Estevez

Reputation: 1084

I may suggest a method for this:

  1. Script the tables, without indicis (indexes).
  2. recreate the tables in an other database
  3. use a sql comparer tool, to make the index creating scripts based on the two database (maybe if you have some tool like that, that tool may already have a feature for this.)

Upvotes: 3

Related Questions