October 1, 2009 at 11:39 am
Is there a way to generate only the indexes for an entire DB. If I right click the DB and go to tasks>Generate scripts I can check create indexes but I always get the tables. The reason I'm needing only the indexes is we are migrating from Informix DB to SQL Server and we are using SSIS packages. With a fast load in the package the data transfer is extremely fast with no indexes but slower when indexes exists are the target tables. So after the data move we want to then apply all the required indexes.
Plus our project is in steps, so we are adding new tables daily and it's easier to create the complete script to build our target database using this generate scripts. But it would be a tedious task if we have to create it with indexes and then manually remove the table creation portion to get the index script.
Thanks
Joe
October 1, 2009 at 11:42 am
http://www.sqlservercentral.com/scripts/Miscellaneous/31893/
was written for sql 2005 but probably a decent place to start.
October 1, 2009 at 11:44 am
i have this "SCRIPT ALL INDEXES with Include" saved in my snippets, and supposedly it will also do the INCLUDE syntax, if it was used. I haven't fully tested it yet.
see if this helps you out:
--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 < 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) not in ('sys', 'dt_') --exclude system tables
order by tablename, indexid, indexname
set nocount off
--drop table #tmp_indexes
Lowell
June 10, 2010 at 5:21 am
See my post at the bottom of this page
http://www.sqlservercentral.com/Forums/Topic401784-562-2.aspx
The script generates index statements and incorporates table schemas, file groups, drop statements and fill factor.
December 5, 2013 at 12:40 pm
Sorry to bring up an old post but I thought I'd add to the enclosed script by including schema names...
--1. get all indexes from current db, place in temp table
select
schemaName = s.name,
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
INNER JOINsys.tables t ON i.id = t.object_id
INNER JOINsys.schemas s ON t.schema_id = s.schema_id
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 < 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 [' + schemaName + '].[' + 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 schemaName, tablename, indexid, indexname
set nocount off
drop table #tmp_indexes
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply