September 23, 2007 at 7:53 pm
Comments posted to this topic are about the item Script all indexes as CREATE INDEX statements
Lowell
June 5, 2008 at 2:39 pm
this is a great script. But it does not work if there are Included columns. Example my index looks like this:
CREATE UNIQUE INDEX IDX_indexName ON dbo.TableName
(
Column1
, Column2
)
INCLUDE
(
Column3
, Column4
, Column5
)
But your script creates it as follows:
CREATE UNIQUE INDEX IDX_indexName ON dbo.TableName
(
Column1
, Column2
, Column3
, Column4
, Column5
)
Any clues on how to modify your script to get what I need?
Thanks!
September 30, 2008 at 11:17 pm
Res Sir,
Thanks for this script.
I have i problem that it will not run for sql server 2005 databases having
compatibility level 90.
Kindly request you to send the script which will run on sql server 2005 databases having
compatibility level 90.
Thanking you in advance.
May 13, 2009 at 3:59 pm
did you find a way to get the included columns?
May 31, 2009 at 7:04 am
ORDER BY SYSCOLUMNS.COLID should be ORDER BY sysindexkeys.keyno
Ordering by COlID causes wrong columns order in the key.
May 31, 2009 at 7:57 pm
i contributed that script a while ago, and clearly it's only for SQL 2000;
here's someone elses script that I saved in my snippets that does INCLUDE columns;it does PRINT statements instead of a SELECT, but that's easy to modify; it alos includes the owner/schema, where my original was just assuming dbo.
I'll modify my script to do the same thing and post it after i test it a little bit.
--------------------------------------------------------------------
declare
@object_id int,
@index_id tinyint,
@schema_name sysname,
@table_name sysname,
@index_name sysname,
@type tinyint,
@uniqueness bit,
@indexed_column sysname,
@included_column sysname,
@indexed_columns varchar(max),
@included_columns varchar(max),
@has_included_cols bit,
@is_descending_key bit,
@stmt varchar(max),
@crlf char(2)
set @crlf = char(13) + char(10)
declare indexes cursor
for
select
schema_name = s.name,
table_name = t.name,
index_id = i.index_id,
index_name = i.name,
type = i.type,
uniqueness = i.is_unique
from
sys.schemas s
join sys.tables t on s.schema_id = t.schema_id
join sys.indexes i on t.object_id = i.object_id
where
i.type > 0 -- none -heap
order
by s.name,
t.name,
i.index_id
open indexes
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
while @@fetch_status(-1)
begin
select @object_id = object_id(@schema_name + '.' + @table_name)
set @indexed_columns = '('
declare indexed_columns cursor
for
select
c.name,
ic.is_descending_key
from
sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id
and ic.object_id = c.object_id
where
ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 0
order by
ic.index_column_id
open indexed_columns
fetch indexed_columns
into @indexed_column, @is_descending_key
while @@fetch_status(-1)
begin
set @indexed_columns = @indexed_columns + @indexed_column +
case @is_descending_key when 1 then ' desc ' else '' end + ', '
fetch indexed_columns
into @indexed_column, @is_descending_key
end
close indexed_columns
deallocate indexed_columns
set @indexed_columns = left(@indexed_columns, len(@indexed_columns)-1) + ')'
if exists
(select object_id
from sys.index_columns
where object_id = @object_id
and index_id = @index_id
and is_included_column = 1 )
begin
set @included_columns = 'include ('
declare included_columns cursor
for
select
c.name,
ic.is_descending_key
from
sys.index_columns ic
join sys.columns c on ic.column_id = c.column_id
and ic.object_id = c.object_id
where
ic.object_id = @object_id
and ic.index_id = @index_id
and ic.is_included_column = 1
order by
ic.index_column_id
open included_columns
fetch included_columns
into @included_column, @is_descending_key
while @@fetch_status(-1)
begin
set @included_columns = @included_columns + @included_column +
case @is_descending_key when 1 then ' desc ' else '' end + ', '
fetch included_columns
into @included_column, @is_descending_key
end
close included_columns
deallocate included_columns
set @included_columns = left(@included_columns, len(@included_columns)-1) + ')' + @crlf
end
set @stmt =
'create ' +
case @uniqueness when 1 then 'unique ' else '' end +
case @type when 1 then 'clustered ' else '' end +
'index ' + @index_name + @crlf +
'on ' + @schema_name + '.' + @table_name + @indexed_columns + @crlf +
isnull(@included_columns,'') +
'g' + 'o' + @crlf + @crlf
print @stmt
fetch
indexes
into
@schema_name,
@table_name ,
@index_id ,
@index_name ,
@type ,
@uniqueness
end
close indexes
deallocate indexes
Lowell
June 5, 2009 at 5:23 am
Hi
Thanks for the original query!
I modified it to have includes.
Additionally will create composite index columns in the correct sequence, I found that your original query worked well, however the sequence that composite indexes added the columns in was incorrect.
e.g.
existing index would look like this ->
CREATE INDEX ix_123 on
([COL1], [COL2], [COL3][)
however the output would look like this ->
CREATE INDEX ix_123 on
([COL2], [COL1], [COL3])
--1. get all indexes from current db, place in temp table
select
tablename = object_name(i.id),
tableid = i.id,
indexid = i.indid,
indexname = i.name,
i.status,
isunique = indexproperty (i.id,i.name,'isunique'),
isclustered = indexproperty (i.id,i.name,'isclustered'),
indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')
into #tmp_indexes
from sysindexes i
where i.indid > 0 and i.indid < 255--not certain about this
and (i.status & 64) = 0--existing indexes
--add additional columns to store include and key column lists
alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)
go
--################################################################################################
--2. loop through tables, put include and index columns into variables
declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int
declare index_cursor cursor for
select tableid, indexid from #tmp_indexes
open index_cursor
fetch next from index_cursor into @tableid, @indexid
while @@fetch_status -1
begin
select @isql_key = '', @isql_incl = ''
select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
--key column
@isql_key = case ic.is_included_column
when 0 then
case ic.is_descending_key
when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
else@isql_key + coalesce(sc.name,'') + ' ASC, '
end
else @isql_key end,
--include column
@isql_incl = case ic.is_included_column
when 1 then
case ic.is_descending_key
when 1 then @isql_incl + coalesce(sc.name,'') + ', '
else @isql_incl + coalesce(sc.name,'') + ', '
end
else @isql_incl end
from sysindexes i
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id
where i.indid > 0 and i.indid 1set @isql_key= left(@isql_key, len(@isql_key) -1)
if len(@isql_incl) > 1set @isql_incl= left(@isql_incl, len(@isql_incl) -1)
update #tmp_indexes
set keycolumns = @isql_key,
includes = @isql_incl
where tableid = @tableid and indexid = @indexid
fetch next from index_cursor into @tableid,@indexid
end
close index_cursor
deallocate index_cursor
--remove invalid indexes,ie ones without key columns
delete from #tmp_indexes where keycolumns = ''
--################################################################################################
--3. output the index creation scripts
set nocount on
--separator
select '---------------------------------------------------------------------'
--create index scripts (for backup)
SELECT
'CREATE '
+ CASE WHEN ISUNIQUE= 1 THEN 'UNIQUE ' ELSE '' END
+ CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
+ 'INDEX [' + INDEXNAME + ']'
+' ON [' + TABLENAME + '] '
+ '(' + keycolumns + ')'
+ CASE
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN ''
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)'
WHEN INDEXFILLFACTOR 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'
ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'
END
FROM #tmp_indexes
where left(tablename,3) not in ('sys', 'dt_')--exclude system tables
order by tablename, indexid, indexname
set nocount off
--drop table #tmp_indexes
June 5, 2009 at 5:27 am
Ah additionally I have added fill factor, and a really cool addition- with ONLINE = ON for nonclustered indexes (so you don't lock the production table when adding the index).
Note: ONLINE = ON doesn't work for clustered indexes.
March 9, 2010 at 4:22 pm
Thank you very much for posting these scripts.
I had scripts for SQL 2000 but nothing that handled include columns
This is really appreciated!
March 9, 2010 at 4:36 pm
Thanks Lowell and Thorv.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 6, 2010 at 10:46 am
Nice script...
Added back the ability to include or exclude PK and Unique indexes, as well as schema if your not using the default dbo. Also now creates the drop statement as well.
--1. get all indexes from current db, place in temp table
select
tablename = schemas.name + '].[' + object_name(i.id),
tableid = i.id,
indexid = i.indid,
indexname = i.name,
i.status,
isunique = indexproperty (i.id,i.name,'isunique'),
isclustered = indexproperty (i.id,i.name,'isclustered'),
indexfillfactor = indexproperty (i.id,i.name,'indexfillfactor')
into #tmp_indexes
from sysindexes i
JOIN sys.objects ON i.id = objects.object_id
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
where i.indid > 0 and i.indid < 255 --not certain about this
and (i.status & 64) = 0
AND INDEXPROPERTY (i.id,i.name,'ISUNIQUE') =0 --comment out to include unique and
AND INDEXPROPERTY (i.id,i.name,'ISCLUSTERED') =0 --include PK's
--add additional columns to store include and key column lists
alter table #tmp_indexes add keycolumns varchar(4000) COLLATE Latin1_General_BIN2, includes varchar(4000)COLLATE Latin1_General_BIN2
go
--################################################################################################
--2. loop through tables, put include and index columns into variables
declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int
declare index_cursor cursor for
select tableid, indexid from #tmp_indexes
open index_cursor
fetch next from index_cursor into @tableid, @indexid
while @@fetch_status <> -1
begin
select @isql_key = '', @isql_incl = ''
select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
--key column
@isql_key = case ic.is_included_column
when 0 then
case ic.is_descending_key
when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
else @isql_key + coalesce(sc.name,'') + ' ASC, '
end
else @isql_key end,
--include column
@isql_incl = case ic.is_included_column
when 1 then
case ic.is_descending_key
when 1 then @isql_incl + coalesce(sc.name,'') + ', '
else @isql_incl + coalesce(sc.name,'') + ', '
end
else @isql_incl end
from sysindexes i
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id
where i.indid > 0 and i.indid < 255
and (i.status & 64) = 0
and i.id = @tableid and i.indid = @indexid
order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end
if len(@isql_key) > 1 set @isql_key = left(@isql_key, len(@isql_key) -1)
if len(@isql_incl) > 1 set @isql_incl = left(@isql_incl, len(@isql_incl) -1)
update #tmp_indexes
set keycolumns = @isql_key,
includes = @isql_incl
where tableid = @tableid and indexid = @indexid
fetch next from index_cursor into @tableid,@indexid
end
close index_cursor
deallocate index_cursor
--remove invalid indexes,ie ones without key columns
delete from #tmp_indexes where keycolumns = ''
--################################################################################################
--3. output the index creation scripts
set nocount on
--separator
select '---------------------------------------------------------------------'
--create index scripts (for backup)
SELECT
'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END
+ CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
+ 'INDEX [' + INDEXNAME + ']'
+' ON [' + TABLENAME + '] '
+ '(' + keycolumns + ')'
+ CASE
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN ''
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON)'
WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') WITH (ONLINE = ON)'
ELSE ' INCLUDE(' + INCLUDES + ') WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'
END
FROM #tmp_indexes
where left(tablename,3)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_') --exclude system tables
order by tablename, indexid, indexname COLLATE Latin1_General_BIN2
set nocount off
--drop table #tmp_indexes
--makes the drop
SELECT 'DROP INDEX '
+' [' + tablename + '].['
+ indexname + ']'
FROM #tmp_indexes where left(tablename,3)COLLATE Latin1_General_BIN2 not in ('sys', 'dt_')
January 13, 2011 at 3:36 am
I made a little change to the last script to also include the where clause of a filtered index. I also removed the collate because this didn't work for me.
--1. get all indexes from current db, place in temp table
select
tablename = schemas.name + '].[' + object_name(i.object_id),
tableid = i.object_id,
indexid = i.index_id,
indexname = i.name,
isunique = indexproperty (i.object_id,i.name,'isunique'),
isclustered = indexproperty (i.object_id,i.name,'isclustered'),
indexfillfactor = indexproperty (i.object_id,i.name,'indexfillfactor'),
CASE WHEN i.filter_definition IS NULL THEN '' ELSE ' WHERE '+i.filter_definition END Filter_Definition
into #tmp_indexes
from sys.indexes i
JOIN sys.objects ON i.object_id = objects.object_id
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
where i.index_id > 0 and i.index_id < 255 --not certain about this
--AND INDEXPROPERTY (i.object_id,i.name,'ISUNIQUE') = 0 --comment out to include unique and
AND INDEXPROPERTY (i.object_id,i.name,'ISCLUSTERED') =0 --include PK's
--add additional columns to store include and key column lists
alter table #tmp_indexes add keycolumns varchar(4000), includes varchar(4000)
go
--################################################################################################
--2. loop through tables, put include and index columns into variables
declare @isql_key varchar(4000), @isql_incl varchar(4000), @tableid int, @indexid int
declare index_cursor cursor for
select tableid, indexid from #tmp_indexes
open index_cursor
fetch next from index_cursor into @tableid, @indexid
while @@fetch_status <> -1
begin
select @isql_key = '', @isql_incl = ''
select --i.name, sc.colid, sc.name, ic.index_id, ic.object_id, *
--key column
@isql_key = case ic.is_included_column
when 0 then
case ic.is_descending_key
when 1 then @isql_key + coalesce(sc.name,'') + ' DESC, '
else @isql_key + coalesce(sc.name,'') + ' ASC, '
end
else @isql_key end,
--include column
@isql_incl = case ic.is_included_column
when 1 then
case ic.is_descending_key
when 1 then @isql_incl + coalesce(sc.name,'') + ', '
else @isql_incl + coalesce(sc.name,'') + ', '
end
else @isql_incl end
from sysindexes i
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0 and (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0)) AND (ic.index_id=CAST(i.indid AS int) AND ic.object_id=i.id)
INNER JOIN sys.columns AS sc ON sc.object_id = ic.object_id and sc.column_id = ic.column_id
where i.indid > 0 and i.indid < 255
and (i.status & 64) = 0
and i.id = @tableid and i.indid = @indexid
order by i.name, case ic.is_included_column when 1 then ic.index_column_id else ic.key_ordinal end
if len(@isql_key) > 1 set @isql_key = left(@isql_key, len(@isql_key) -1)
if len(@isql_incl) > 1 set @isql_incl = left(@isql_incl, len(@isql_incl) -1)
update #tmp_indexes
set keycolumns = @isql_key,
includes = @isql_incl
where tableid = @tableid and indexid = @indexid
fetch next from index_cursor into @tableid,@indexid
end
close index_cursor
deallocate index_cursor
--remove invalid indexes,ie ones without key columns
delete from #tmp_indexes where keycolumns = ''
--################################################################################################
--3. output the index creation scripts
set nocount on
--separator
select '---------------------------------------------------------------------'
--create index scripts (for backup)
SELECT
'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END
+ CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
+ 'INDEX [' + INDEXNAME + ']'
+' ON [' + TABLENAME + '] '
+ '(' + keycolumns + ')'
+ CASE
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN Filter_Definition
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON)'
WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') '+Filter_Definition+' WITH (ONLINE = ON)'
ELSE ' INCLUDE(' + INCLUDES + ') '+Filter_Definition+' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'
END
FROM #tmp_indexes
where left(tablename,3) not in ('sys', 'dt_') --exclude system tables
order by tablename, indexid, indexname
--makes the drop
SELECT 'DROP INDEX '
+' [' + tablename + '].['
+ indexname + ']'
FROM #tmp_indexes where left(tablename,3) not in ('sys', 'dt_')
--Drop the temp table again
DROP TABLE #tmp_indexes
set nocount off
March 17, 2011 at 9:17 am
wow this script has come a long way since the original version; thanks again to everyone who has contributed;
I cleaned up the formatting a little bit and added some comments, so that folks run this against SQL 2005 don't panic when it fails due to the new filtered indexes;
--Script SQL 2008+ Indexes AS CREATE INDEX Statements
--http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx
--original version submitted by anonymous/unknown contributor
--enhanced and contributed to discussion by Lowell
--enhanced by thorv-918308 to correct index columns in the correct sequence
--enhanced by thorv-918308 added fill factor, and with ONLINE = ON
--enhanced by mfuller333 to include DROP statements!
--enhanced by Sander A. to include filtered indexes!
--enhanced by craigborri to put the clustered indexes first, you'll want to do if you ever run this for real
/*if you get this error:
Msg 207, Level 16, State 1, Line 22
Invalid column name 'filter_definition'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'filter_definition'.
Msg 208, Level 16, State 0, Line 11
Invalid object name '#tmp_indexes'.
the issue is the database in question is SQL 2005: filter_definition is SQL 2008
see the inline code to comment/uncomment to make this SQL 2005 compliant.
*/
--################################################################################################
--1. get all indexes from current db, place in temp table
--################################################################################################
SELECT
tablename = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),
tableid = ixz.object_id,
indexid = ixz.index_id,
indexname = ixz.name,
isunique = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),
isclustered = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),
indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor'),
--SQL2008+ Filtered indexes:
CASE
WHEN ixz.filter_definition IS NULL
THEN ''
ELSE ' WHERE ' + ixz.filter_definition
END Filter_Definition
--For 2005, which did not have filtered indexes, comment out the above CASE statement, and uncomment this:
--'' AS Filter_Definition
INTO #tmp_indexes
FROM sys.indexes ixz
INNER JOIN sys.objects obz
ON ixz.object_id = obz.object_id
INNER JOIN sys.schemas scmz
ON obz.schema_id = scmz.schema_id
WHERE ixz.index_id > 0
AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISUNIQUE') = 0 -- comment out to include unique and
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 -- comment out to include PK's
--add additional columns to store include and key column lists
ALTER TABLE #tmp_indexes ADD keycolumns VARCHAR(4000), includes VARCHAR(4000)
GO
--################################################################################################
--2. loop through tables, put include and index columns into variables
--################################################################################################
DECLARE @isql_key VARCHAR(4000),
@isql_incl VARCHAR(4000),
@tableid INT,
@indexid INT
DECLARE index_cursor CURSOR
FOR
SELECT
tableid,
indexid
FROM #tmp_indexes
--################################################################################################
--Cursor Block
--################################################################################################
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @tableid, @indexid
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql_key = '', @isql_incl = ''
SELECT --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *
--key column
@isql_key = CASE ixcolz.is_included_column
WHEN 0
THEN CASE ixcolz.is_descending_key
WHEN 1
THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '
ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '
END
ELSE @isql_key
END,
--include column
@isql_incl = CASE ixcolz.is_included_column
WHEN 1
THEN CASE ixcolz.is_descending_key
WHEN 1
THEN @isql_incl + COALESCE(colz.name,'') + ', '
ELSE @isql_incl + COALESCE(colz.name,'') + ', '
END
ELSE @isql_incl
END
FROM sysindexes ixz
INNER JOIN sys.index_columns AS ixcolz
ON (ixcolz.column_id > 0
AND ( ixcolz.key_ordinal > 0
OR ixcolz.partition_ordinal = 0
OR ixcolz.is_included_column != 0)
)
AND ( ixcolz.index_id=CAST(ixz.indid AS INT)
AND ixcolz.object_id=ixz.id
)
INNER JOIN sys.columns AS colz
ON colz.object_id = ixcolz.object_id
AND colz.column_id = ixcolz.column_id
WHERE ixz.indid > 0 AND ixz.indid < 255
AND (ixz.status & 64) = 0
AND ixz.id = @tableid
AND ixz.indid = @indexid
ORDER BY
ixz.name,
CASE ixcolz.is_included_column
WHEN 1
THEN ixcolz.index_column_id
ELSE ixcolz.key_ordinal
END
--remove any trailing commas from the cursor results
IF LEN(@isql_key) > 1 SET @isql_key = LEFT(@isql_key, LEN(@isql_key) -1)
IF LEN(@isql_incl) > 1 SET @isql_incl = LEFT(@isql_incl, LEN(@isql_incl) -1)
--put the columns collection into our temp table
UPDATE #tmp_indexes
SET keycolumns = @isql_key,
includes = @isql_incl
WHERE tableid = @tableid
AND indexid = @indexid
FETCH NEXT FROM index_cursor INTO @tableid,@indexid
END --WHILE
--################################################################################################
--End Cursor Block
--################################################################################################
CLOSE index_cursor
DEALLOCATE index_cursor
--remove invalid indexes, ie ones without key columns
DELETE FROM #tmp_indexes WHERE keycolumns = ''
--################################################################################################
--3. output the index creation scripts
--################################################################################################
SET NOCOUNT ON
--separator for results-to-text
SELECT '---------------------------------------------------------------------'
--create index scripts (for backup)
SELECT 'IF NOT EXISTS(SELECT OBJECT_ID(' + INDEXNAME + ')' + ' ' +
'CREATE '
+ CASE WHEN ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END
+ CASE WHEN ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
+ 'INDEX ' + QUOTENAME(INDEXNAME)
+ ' ON ' + (TABLENAME) + ' '
+ '(' + keycolumns + ')'
+ CASE
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 1 AND INCLUDES = '' THEN Filter_Definition
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON)'
WHEN INDEXFILLFACTOR <> 0 AND ISCLUSTERED = 0 AND INCLUDES = '' THEN Filter_Definition + ' WITH (ONLINE = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ')'
WHEN INDEXFILLFACTOR = 0 AND ISCLUSTERED = 0 AND INCLUDES <> '' THEN ' INCLUDE (' + INCLUDES + ') ' + Filter_Definition + ' WITH (ONLINE = ON)'
ELSE ' INCLUDE(' + INCLUDES + ') ' + Filter_Definition + ' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),INDEXFILLFACTOR) + ', ONLINE = ON)'
END
FROM #tmp_indexes
WHERE LEFT(tablename,3) NOT IN ('sys', 'dt_') --exclude system tables
ORDER BY
ISCLUSTERED desc,
tablename,
indexid,
indexname
--makes the drop
SELECT
'DROP INDEX '
+ ' ' + (tablename) + '.'
+ (indexname) + ''
FROM #tmp_indexes
WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_')
--Drop the temp table again
DROP TABLE #tmp_indexes
SET NOCOUNT OFF
Lowell
April 12, 2011 at 8:15 am
--Script SQL 2008+ Indexes AS CREATE INDEX Statements
--http://www.sqlservercentral.com/Forums/Topic401795-566-1.aspx
--original version submitted by anonymous/unknown contributor
--enhanced and contributed to discussion by Lowell
--enhanced by thorv-918308 to correct index columns in the correct sequence
--enhanced by thorv-918308 added fill factor, and with ONLINE = ON
--enhanced by mfuller333 to include DROP statements!
--enhanced by Sander A. to include filtered indexes!
--enhanced a tiny bit to include filegroups. Does not script partitioned indexes for now.
/*if you get this error:
Msg 207, Level 16, State 1, Line 22
Invalid column name 'filter_definition'.
Msg 207, Level 16, State 1, Line 24
Invalid column name 'filter_definition'.
Msg 208, Level 16, State 0, Line 11
Invalid object name '#tmp_indexes'.
the issue is the database in question is SQL 2005: filter_definition is SQL 2008
see the inline code to comment/uncomment to make this SQL 2005 compliant.
*/
--################################################################################################
--1. get all indexes from current db, place in temp table
--################################################################################################
SELECT
ixz.object_id,
tablename = QUOTENAME(scmz.name) + '.' + QUOTENAME((OBJECT_NAME(ixz.object_id))),
tableid = ixz.object_id,
indexid = ixz.index_id,
indexname = ixz.name,
isunique = INDEXPROPERTY (ixz.object_id,ixz.name,'isunique'),
isclustered = INDEXPROPERTY (ixz.object_id,ixz.name,'isclustered'),
indexfillfactor = INDEXPROPERTY (ixz.object_id,ixz.name,'indexfillfactor'),
--SQL2008+ Filtered indexes:
CASE
WHEN ixz.filter_definition IS NULL
THEN ''
ELSE ' WHERE ' + ixz.filter_definition
END Filter_Definition
--For 2005, which did not have filtered indexes, comment out the above CASE statement, and uncomment this:
--'' AS Filter_Definition
INTO #tmp_indexes
FROM sys.indexes ixz
INNER JOIN sys.objects obz
ON ixz.object_id = obz.object_id
INNER JOIN sys.schemas scmz
ON obz.schema_id = scmz.schema_id
WHERE ixz.index_id > 0
AND ixz.index_id < 255 ---- 0 = HEAP index, 255 = TEXT columns index
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISUNIQUE') = 0 -- comment out to include unique and
AND INDEXPROPERTY (ixz.object_id,ixz.name,'ISCLUSTERED') = 0 -- comment out to include PK's
--add additional columns to store include and key column lists
ALTER TABLE #tmp_indexes ADD keycolumns VARCHAR(4000), includes VARCHAR(4000)
GO
--################################################################################################
--2. loop through tables, put include and index columns into variables
--################################################################################################
DECLARE @isql_key VARCHAR(4000),
@isql_incl VARCHAR(4000),
@tableid INT,
@indexid INT
DECLARE index_cursor CURSOR
FOR
SELECT
tableid,
indexid
FROM #tmp_indexes
--################################################################################################
--Cursor Block
--################################################################################################
OPEN index_cursor
FETCH NEXT FROM index_cursor INTO @tableid, @indexid
WHILE @@FETCH_STATUS <> -1
BEGIN
SELECT @isql_key = '', @isql_incl = ''
SELECT --ixz.name, colz.colid, colz.name, ixcolz.index_id, ixcolz.object_id, *
--key column
@isql_key = CASE ixcolz.is_included_column
WHEN 0
THEN CASE ixcolz.is_descending_key
WHEN 1
THEN @isql_key + COALESCE(colz.name,'') + ' DESC, '
ELSE @isql_key + COALESCE(colz.name,'') + ' ASC, '
END
ELSE @isql_key
END,
--include column
@isql_incl = CASE ixcolz.is_included_column
WHEN 1
THEN CASE ixcolz.is_descending_key
WHEN 1
THEN @isql_incl + COALESCE(colz.name,'') + ', '
ELSE @isql_incl + COALESCE(colz.name,'') + ', '
END
ELSE @isql_incl
END
FROM sysindexes ixz
INNER JOIN sys.index_columns AS ixcolz
ON (ixcolz.column_id > 0
AND ( ixcolz.key_ordinal > 0
OR ixcolz.partition_ordinal = 0
OR ixcolz.is_included_column != 0)
)
AND ( ixcolz.index_id=CAST(ixz.indid AS INT)
AND ixcolz.object_id=ixz.id
)
INNER JOIN sys.columns AS colz
ON colz.object_id = ixcolz.object_id
AND colz.column_id = ixcolz.column_id
WHERE ixz.indid > 0 AND ixz.indid < 255
AND (ixz.status & 64) = 0
AND ixz.id = @tableid
AND ixz.indid = @indexid
ORDER BY
ixz.name,
CASE ixcolz.is_included_column
WHEN 1
THEN ixcolz.index_column_id
ELSE ixcolz.key_ordinal
END
--remove any trailing commas from the cursor results
IF LEN(@isql_key) > 1 SET @isql_key = LEFT(@isql_key, LEN(@isql_key) -1)
IF LEN(@isql_incl) > 1 SET @isql_incl = LEFT(@isql_incl, LEN(@isql_incl) -1)
--put the columns collection into our temp table
UPDATE #tmp_indexes
SET keycolumns = @isql_key,
includes = @isql_incl
WHERE tableid = @tableid
AND indexid = @indexid
FETCH NEXT FROM index_cursor INTO @tableid,@indexid
END --WHILE
--################################################################################################
--End Cursor Block
--################################################################################################
CLOSE index_cursor
DEALLOCATE index_cursor
--remove invalid indexes, ie ones without key columns
DELETE FROM #tmp_indexes WHERE keycolumns = ''
--################################################################################################
--3. output the index creation scripts
--################################################################################################
SET NOCOUNT ON
--separator for results-to-text
SELECT '---------------------------------------------------------------------'
--create index scripts (for backup)
--IF NOT EXISTS (SELECT * FROM dbo.sysindexes WHERE id = OBJECT_ID(N' + '''' +
-- '[dbo].[' + tablename + ']' + '''' +
--') AND name = N' + '''' +
-- indexname + '''' + ')' +
SELECT
'IF NOT EXISTS(SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + ti.TABLENAME + '''' + ') AND name = N' + '''' + ti.INDEXNAME + '''' + ')' + ' ' +
'CREATE '
+ CASE WHEN ti.ISUNIQUE = 1 THEN 'UNIQUE ' ELSE '' END
+ CASE WHEN ti.ISCLUSTERED = 1 THEN 'CLUSTERED ' ELSE '' END
+ 'INDEX ' + QUOTENAME(ti.INDEXNAME)
+ ' ON ' + (ti.TABLENAME) + ' '
+ '(' + ti.keycolumns + ')'
+ CASE
WHEN ti.INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 1 AND INCLUDES = '' THEN ti.Filter_Definition + ' WITH (SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'
WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' THEN ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'
WHEN INDEXFILLFACTOR <> 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES = '' THEN ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON, FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ') ON [' + fg.name + ']'
WHEN INDEXFILLFACTOR = 0 AND ti.ISCLUSTERED = 0 AND ti.INCLUDES <> '' THEN ' INCLUDE (' + ti.INCLUDES + ') ' + ti.Filter_Definition + ' WITH (ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'
ELSE ' INCLUDE(' + ti.INCLUDES + ') ' + ti.Filter_Definition + ' WITH (FILLFACTOR = ' + CONVERT(VARCHAR(10),ti.INDEXFILLFACTOR) + ', ONLINE = ON, SORT_IN_TEMPDB = ON) ON [' + fg.name + ']'
END
FROM #tmp_indexes ti
JOIN sys.indexes i ON ti.Object_id = i.object_id and ti.indexname = i.name
JOIN sys.filegroups fg on i.data_space_id = fg.data_space_id
WHERE LEFT(ti.tablename,3) NOT IN ('sys', 'dt_') --exclude system tables
ORDER BY
ti.tablename,
ti.indexid,
ti.indexname
--makes the drop
SELECT
'DROP INDEX '
+ ' ' + (tablename) + '.'
+ (indexname) + ''
FROM #tmp_indexes
WHERE LEFT(tablename,4) NOT IN ('[sys', 'dt_')
----Drop the temp table again
DROP TABLE #tmp_indexes
--SET NOCOUNT OFF
April 22, 2011 at 12:07 pm
I'd suggest changing the order by on the last select to
order by ISCLUSTERED desc, tablename, indexid, indexname
This will insure the clustered indexes are created first, which you'll want to do if you ever run this for real.
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply