Robin Hames

Hints, tricks and tips relating to MS SQL Server and .NET
posts - 14 , comments - 44 , trackbacks - 0

SQL server script to generate CREATE INDEX commands

This script generates the “CREATE INDEX” scripts for a SQL Server database:
 
set nocount on
 
declare @index table
(
      object_id int,
      objectName sysname,
      index_id int,
      indexName sysname,
      fill_factor tinyint,
      allow_row_locks bit,
      allow_page_locks bit,
      is_padded bit,
      indexText varchar(max),
      indexTextEnd varchar(max)
)
 
declare @indexColumn table
(
      object_id int,
      index_id int,
      column_id int,
      index_column_id int,
      max_index_column_id int,
      is_descending_key bit,
      is_included_column bit,
      columnName varchar(255),
      indexText varchar(max) null
)
 
insert into @index
select
      i.object_id,
      object_name(i.object_id),
      i.index_id,
      i.name,
      fill_factor,
      allow_row_locks,
      allow_page_locks,
      is_padded,
      'CREATE NONCLUSTERED INDEX [' + i.name + '] ON [dbo].[' + object_name(i.object_id) + '] ' + char(13),
      'WITH (PAD_INDEX = ' +
            CASE WHEN is_padded = 1 THEN ' ON ' ELSE ' OFF ' END +
            ', STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ' +
            CASE WHEN allow_row_locks = 1 THEN ' ON ' ELSE ' OFF ' END +
            ', ALLOW_PAGE_LOCKS = ' +
            CASE WHEN allow_page_locks = 1 THEN ' ON ' ELSE ' OFF ' END +
            CASE WHEN fill_factor > 0 THEN ', FILLFACTOR = ' + convert(varchar(3), fill_factor) ELSE '' END +
            ')' + CHAR(13)
from sys.indexes i
where i.type = 2 and i.is_unique_constraint = 0
and objectproperty(i.object_id , 'IsUserTable') = 1
order by object_name(i.object_id), i.name
 
insert into @indexColumn
select
      i.object_id,
      i.index_id,
      ic.column_id,
      ic.index_column_id,
      max(ic.index_column_id) over (partition by      i.object_id, i.index_id, is_included_column),
      is_descending_key,
      is_included_column,
      '[' + c.name + ']',
      null
from @index i
join sys.index_columns ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
join sys.columns c
on ic.object_id = c.object_id
and ic.column_id = c.column_id
order by i.object_id, i.index_id, ic.index_column_id
 
 
 
declare @fields varchar(max)
declare @object_id int, @index_id int
 
select @fields = null, @object_id = -1, @index_id = -1
 
update @indexColumn
set @fields = indexText =
      case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)
            then isnull(@fields + ', ', ' ') + columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end
            else columnName + case when is_descending_key = 0 then ' ASC' else ' DESC' end
            end,
      @object_id = case when object_id <> @object_id
            then object_id else @object_id end,
      @index_id = case when index_id <> @index_id
            then index_id else @index_id end
from @indexColumn
where is_included_column = 0
 
select @fields = null, @object_id = -1, @index_id = -1
 
update @indexColumn
set @fields = indexText =
      case when object_id = isnull(@object_id, object_id) and index_id = isnull(@index_id, index_id)
            then isnull(@fields + ', ', ' ') + columnName
            else columnName
            end,
      @object_id = case when object_id <> @object_id
            then object_id else @object_id end,
      @index_id = case when index_id <> @index_id
            then index_id else @index_id end
from @indexColumn
where is_included_column = 1
 
update @index
set indexText = i.indexText + '( ' + char(13) + char(9) + ic.indexText + char(13) + ') '
from @index i join @indexColumn ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
and ic.index_column_id = ic.max_index_column_id
and ic.is_included_column = 0
 
update @index
set indexText = i.indexText + 'INCLUDE ( ' + char(13) + char(9) + ic.indexText + char(13) + ') '
from @index i join @indexColumn ic
on i.object_id = ic.object_id
and i.index_id = ic.index_id
and ic.index_column_id = ic.max_index_column_id
and ic.is_included_column = 1
 
update @index
set indexText = indexText + indexTextEnd
from @index
 
select indexText
--, objectName, indexName 
from @index
 
 

Print | posted on Friday, November 21, 2008 12:30 PM | Filed Under [ SQL Server ]

Feedback

No comments posted yet.
Post A Comment
Title:
Name:
Email:
Comment:
Verification:
 
 

Powered by: