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.
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
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
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.
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
I may suggest a method for this:
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
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
```
