Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Generate Index only scripts for whole DB


Generate Index only scripts for whole DB

Author
Message
joepacelli
joepacelli
SSC Journeyman
SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)SSC Journeyman (92 reputation)

Group: General Forum Members
Points: 92 Visits: 291
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
jsph152
jsph152
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 686
http://www.sqlservercentral.com/scripts/Miscellaneous/31893/

was written for sql 2005 but probably a decent place to start.
Lowell
Lowell
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19383 Visits: 39467
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

--
help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

r5d4
r5d4
Old Hand
Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)Old Hand (305 reputation)

Group: General Forum Members
Points: 305 Visits: 816
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.
Drammy
Drammy
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 115
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 JOIN sys.tables t ON i.id = t.object_id
INNER JOIN sys.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


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search