How to script Indexes on SQL server DB?

Question

Post reply

How to script Indexes on SQL server DB?

Vidas Zabinskas

Hall of Fame

Points: 3258
More actions
May 23, 2005 at 5:22 am

#167079

I need to drop them all from my database in order to change column collations and then recreate them. Does anyone know any way to do that?
When I try to generate sql script from EM to drop them all from my db the proccess hangs
Thanks in advance for any help

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 1

One question: How to drop all USER defined indexes from database at once? There are about 2300 of them

Thanks in advance

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 2

2300 User created indexes? Good grief, that better be a huge database, or a warehouse...

I think all you can do is:

(1) allow the script to generate - it should do this eventually (you might want to try it on a restored copy on a test server with no other users)

(2) refer to the original scripts that were used to create the indexes (a best practice is to maintain all of these outside your database, for example in a source safe, but I'm fairly sure, given the question, that you don't have this)

(3) a 3rd party tool that might be able to handle this. I've reviewed them very infrequently, but no company I've been with has actually bought the tools something like SQLCompare, but there are many others...

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 3

Well, these 2300 indexes exists on a server, not on one db I've overdone a little; this server holds one real DB and its test_version.

I allready have generated script of indexes, but I cann't find a way how to drop all indexes, pk and fk from particular db.

Regan Galbraith SSCertifiable Points: 5227 More actions · Answer 4

how about:

select 'drop index '+ object_name (si.id) + '.' + si.[name]

from sysindexes si

inner join

sysobjects so

on si.id = so.id

where si.indid not in (0,255)

and so.xtype = 'U'

It may need some work - look into it in a test environment, and then consider saving it, and running them.

Vidas Zabinskas Hall of Fame Points: 3258 More actions · Answer 5

Vidas Zabinskas

Hall of Fame

Points: 3258

May 23, 2005 at 7:46 am

#560488

Thanks Wanderer, after some adds I'll use it

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Can you post it here?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Johan Bijnens SSC Guru Points: 135374 More actions · Answer 7

Keep in mind some of them are created by implementing a constraint and need to be removed by drop constraint.

Here 's a little testcase to show how you can do it and after the drop, you'll still need to handle your "normal" indexes. If you have the create-ddls, you only need the drop-ix-generation like Wanderer suggested or using sp_helpindex to capture all into a temp-tb and then generate the ddl.

--TEST IT -- TEST IT -- TEST IT --

-- creatie testcase begin

if object_id('T_ParentMC') is null

begin

create table T_ParentMC (

col1 int identity(1,1) not null ,

col2 int not null,

col3 int not null,

col4 int not null,

col5 varchar(100) not null default ''

, CONSTRAINT PK_ParentMC PRIMARY KEY (col2,col3,col4)

&nbsp

print 'table T_ParentMC created'

end

go

if object_id('T_ChildMC') is null

begin

create table T_ChildMC (

col1 int identity(1,1) not null primary key,

col2FK1_1 int not null,

col3FK1_2 int not null,

col4FK1_3 int not null,

col5 varchar(100) not null default ''

, CONSTRAINT FK_C2P FOREIGN KEY (col2FK1_1, col3FK1_2, col4FK1_3)

REFERENCES T_ParentMC (col2,col3,col4)

&nbsp

print 'table T_ChildMC created'

end

go

-- creatie testgevalleke einde

Declare @ParentTbName varchar(128)

, @ParentTbOwner varchar(128)

select @ParentTbName = 'T_ParentMC', @ParentTbOwner = NULL

SET nocount on

create table #tmpPKeys(

TABLE_QUALIFIER sysname,

TABLE_OWNER sysname not null,

TABLE_NAME sysname not null,

COLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

PK_NAME sysname null )

Create index ix#tmpPKeys on #tmpPKeys (TABLE_QUALIFIER, TABLE_OWNER,TABLE_NAME, KEY_SEQ)

-- Get PK-info

insert into #tmpPKeys

exec sp_pkeys @table_name = @ParentTbName

, @table_owner = @ParentTbOwner

-- [ , [ @table_qualifier = ] 'qualifier' ] -- DBName

create table #tmpFKeys

(PKTABLE_QUALIFIER sysname not null,

PKTABLE_OWNER sysname not null,

PKTABLE_NAME sysname not null,

PKCOLUMN_NAME sysname not null,

FKTABLE_QUALIFIER sysname not null,

FKTABLE_OWNER sysname not null,

FKTABLE_NAME sysname not null,

FKCOLUMN_NAME sysname not null,

KEY_SEQ smallint not null,

UPDATE_RULE smallint not null,

DELETE_RULE smallint not null,

FK_NAME sysname not null,

PK_NAME sysname not null,

DEFERRABILITY int not null)

Create index #tmpFKeys on #tmpFKeys (FK_NAME, KEY_SEQ)

-- Get FK-info (all dependant objects)

insert into #tmpFKeys

exec sp_fkeys @pktable_name = @ParentTbName

, @pktable_owner = @ParentTbOwner

-- [ , [ @pktable_qualifier = ] 'pktable_qualifier' ]

-- { , [ @fktable_name = ] 'fktable_name' }

-- [ , [ @fktable_owner = ] 'fktable_owner' ]

-- [ , [ @fktable_qualifier = ] 'fktable_qualifier' ]

print 'Begin transaction trxAlter_' + @ParentTbName

print ' '

print '-- Drop Referencing constraints'

select 'Alter Table [' + FKTABLE_OWNER + '].[' + FKTABLE_NAME + '] drop constraint [' + FK_NAME + ']' + char(13) + 'GO '

from #tmpFKeys

where Key_SEQ = 1

order by FKTABLE_OWNER, FKTABLE_NAME, FK_NAME

print '-- drop PK-constraint'

select 'Alter Table [' + TABLE_OWNER + '].[' + TABLE_NAME + '] drop constraint [' + PK_NAME + ']' + char(13) + 'GO '

from #tmpPKeys

where Key_SEQ = 1

order by TABLE_OWNER, TABLE_NAME

----------------------------

generate your drop-normal-indexes (and their create-ddl) here !!!

----------------------------

print '-- Create PK_Constraint'

select 'Alter Table [' + P1.TABLE_OWNER + '].[' + P1.TABLE_NAME + '] WITH NOCHECK add constraint ' + P1.PK_NAME + ' PRIMARY KEY CLUSTERED ( '+

P1.COLUMN_NAME

+ case when P2.COLUMN_NAME is null then '' else ', ' + P2.COLUMN_NAME end

+ case when P3.COLUMN_NAME is null then '' else ', ' + P3.COLUMN_NAME end

+ case when P4.COLUMN_NAME is null then '' else ', ' + P4.COLUMN_NAME end

+ case when P5.COLUMN_NAME is null then '' else ', ' + P5.COLUMN_NAME end

+ case when P6.COLUMN_NAME is null then '' else ', ' + P6.COLUMN_NAME end

+ case when P7.COLUMN_NAME is null then '' else ', ' + P7.COLUMN_NAME end

+ case when P8.COLUMN_NAME is null then '' else ', ' + P8.COLUMN_NAME end

+ case when P9.COLUMN_NAME is null then '' else ', ' + P9.COLUMN_NAME end

+ case when P10.COLUMN_NAME is null then '' else ', ' + P10.COLUMN_NAME end

+ case when P11.COLUMN_NAME is null then '' else ', ' + P11.COLUMN_NAME end

+ case when P12.COLUMN_NAME is null then '' else ', ' + P12.COLUMN_NAME end

+ case when P13.COLUMN_NAME is null then '' else ', ' + P13.COLUMN_NAME end

+ case when P14.COLUMN_NAME is null then '' else ', ' + P14.COLUMN_NAME end

+ case when P15.COLUMN_NAME is null then '' else ', ' + P15.COLUMN_NAME end

+ case when P16.COLUMN_NAME is null then '' else ', ' + P16.COLUMN_NAME end

+ case when P17.COLUMN_NAME is null then '' else ', ' + P17.COLUMN_NAME end

+ case when P18.COLUMN_NAME is null then '' else ', ' + P18.COLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpPKeys P1

left join #tmpPKeys P2

on P1.TABLE_OWNER = P2.TABLE_OWNER and P1.TABLE_NAME = P2.TABLE_NAME and P1.KEY_SEQ = 1 and P2.KEY_SEQ = 2

left join #tmpPKeys P3

on P1.TABLE_OWNER = P3.TABLE_OWNER and P1.TABLE_NAME = P3.TABLE_NAME and P1.KEY_SEQ = 1 and P3.KEY_SEQ = 3

left join #tmpPKeys P4

on P1.TABLE_OWNER = P4.TABLE_OWNER and P1.TABLE_NAME = P4.TABLE_NAME and P1.KEY_SEQ = 1 and P4.KEY_SEQ = 4

left join #tmpPKeys P5

on P1.TABLE_OWNER = P5.TABLE_OWNER and P1.TABLE_NAME = P5.TABLE_NAME and P1.KEY_SEQ = 1 and P5.KEY_SEQ = 5

left join #tmpPKeys P6

on P1.TABLE_OWNER = P6.TABLE_OWNER and P1.TABLE_NAME = P6.TABLE_NAME and P1.KEY_SEQ = 1 and P6.KEY_SEQ = 6

left join #tmpPKeys P7

on P1.TABLE_OWNER = P7.TABLE_OWNER and P1.TABLE_NAME = P7.TABLE_NAME and P1.KEY_SEQ = 1 and P7.KEY_SEQ = 7

left join #tmpPKeys P8

on P1.TABLE_OWNER = P8.TABLE_OWNER and P1.TABLE_NAME = P8.TABLE_NAME and P1.KEY_SEQ = 1 and P8.KEY_SEQ = 8

left join #tmpPKeys P9

on P1.TABLE_OWNER = P9.TABLE_OWNER and P1.TABLE_NAME = P9.TABLE_NAME and P1.KEY_SEQ = 1 and P9.KEY_SEQ = 9

left join #tmpPKeys P10

on P1.TABLE_OWNER = P10.TABLE_OWNER and P1.TABLE_NAME = P10.TABLE_NAME and P1.KEY_SEQ = 1 and P10.KEY_SEQ = 10

left join #tmpPKeys P11

on P1.TABLE_OWNER = P11.TABLE_OWNER and P1.TABLE_NAME = P11.TABLE_NAME and P1.KEY_SEQ = 1 and P11.KEY_SEQ = 11

left join #tmpPKeys P12

on P1.TABLE_OWNER = P12.TABLE_OWNER and P1.TABLE_NAME = P12.TABLE_NAME and P1.KEY_SEQ = 1 and P12.KEY_SEQ = 12

left join #tmpPKeys P13

on P1.TABLE_OWNER = P13.TABLE_OWNER and P1.TABLE_NAME = P13.TABLE_NAME and P1.KEY_SEQ = 1 and P13.KEY_SEQ = 13

left join #tmpPKeys P14

on P1.TABLE_OWNER = P14.TABLE_OWNER and P1.TABLE_NAME = P14.TABLE_NAME and P1.KEY_SEQ = 1 and P14.KEY_SEQ = 14

left join #tmpPKeys P15

on P1.TABLE_OWNER = P15.TABLE_OWNER and P1.TABLE_NAME = P15.TABLE_NAME and P1.KEY_SEQ = 1 and P15.KEY_SEQ = 15

left join #tmpPKeys P16

on P1.TABLE_OWNER = P16.TABLE_OWNER and P1.TABLE_NAME = P16.TABLE_NAME and P1.KEY_SEQ = 1 and P16.KEY_SEQ = 16

left join #tmpPKeys P17

on P1.TABLE_OWNER = P17.TABLE_OWNER and P1.TABLE_NAME = P17.TABLE_NAME and P1.KEY_SEQ = 1 and P17.KEY_SEQ = 17

left join #tmpPKeys P18

on P1.TABLE_OWNER = P18.TABLE_OWNER and P1.TABLE_NAME = P18.TABLE_NAME and P1.KEY_SEQ = 1 and P18.KEY_SEQ = 18

where P1.KEY_SEQ = 1

order by P1.TABLE_OWNER, P1.TABLE_NAME

print '-- Create FK-Constraints'

print '-- keep FK column-ordinal equal to PK column-ordinal'

select 'Alter Table [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] WITH NOCHECK add constraint [' + FK1.FK_NAME + '] FOREIGN KEY ( '+

FK1.FKCOLUMN_NAME

+ case when FK2.FKCOLUMN_NAME is null then '' else ', ' + FK2.FKCOLUMN_NAME end

+ case when FK3.FKCOLUMN_NAME is null then '' else ', ' + FK3.FKCOLUMN_NAME end

+ case when FK4.FKCOLUMN_NAME is null then '' else ', ' + FK4.FKCOLUMN_NAME end

+ case when FK5.FKCOLUMN_NAME is null then '' else ', ' + FK5.FKCOLUMN_NAME end

+ case when FK6.FKCOLUMN_NAME is null then '' else ', ' + FK6.FKCOLUMN_NAME end

+ case when FK7.FKCOLUMN_NAME is null then '' else ', ' + FK7.FKCOLUMN_NAME end

+ case when FK8.FKCOLUMN_NAME is null then '' else ', ' + FK8.FKCOLUMN_NAME end

+ case when FK9.FKCOLUMN_NAME is null then '' else ', ' + FK9.FKCOLUMN_NAME end

+ case when FK10.FKCOLUMN_NAME is null then '' else ', ' + FK10.FKCOLUMN_NAME end

+ case when FK11.FKCOLUMN_NAME is null then '' else ', ' + FK11.FKCOLUMN_NAME end

+ case when FK12.FKCOLUMN_NAME is null then '' else ', ' + FK12.FKCOLUMN_NAME end

+ case when FK13.FKCOLUMN_NAME is null then '' else ', ' + FK13.FKCOLUMN_NAME end

+ case when FK14.FKCOLUMN_NAME is null then '' else ', ' + FK14.FKCOLUMN_NAME end

+ case when FK15.FKCOLUMN_NAME is null then '' else ', ' + FK15.FKCOLUMN_NAME end

+ case when FK16.FKCOLUMN_NAME is null then '' else ', ' + FK16.FKCOLUMN_NAME end

+ case when FK17.FKCOLUMN_NAME is null then '' else ', ' + FK17.FKCOLUMN_NAME end

+ case when FK18.FKCOLUMN_NAME is null then '' else ', ' + FK18.FKCOLUMN_NAME end

+ ' ) REFERENCES [' + FK1.PKTABLE_OWNER + '].[' + FK1.PKTABLE_NAME + '] ( ' +

FK1.PKCOLUMN_NAME

+ case when FK2.PKCOLUMN_NAME is null then '' else ', ' + FK2.PKCOLUMN_NAME end

+ case when FK3.PKCOLUMN_NAME is null then '' else ', ' + FK3.PKCOLUMN_NAME end

+ case when FK4.PKCOLUMN_NAME is null then '' else ', ' + FK4.PKCOLUMN_NAME end

+ case when FK5.PKCOLUMN_NAME is null then '' else ', ' + FK5.PKCOLUMN_NAME end

+ case when FK6.PKCOLUMN_NAME is null then '' else ', ' + FK6.PKCOLUMN_NAME end

+ case when FK7.PKCOLUMN_NAME is null then '' else ', ' + FK7.PKCOLUMN_NAME end

+ case when FK8.PKCOLUMN_NAME is null then '' else ', ' + FK8.PKCOLUMN_NAME end

+ case when FK9.PKCOLUMN_NAME is null then '' else ', ' + FK9.PKCOLUMN_NAME end

+ case when FK10.PKCOLUMN_NAME is null then '' else ', ' + FK10.PKCOLUMN_NAME end

+ case when FK11.PKCOLUMN_NAME is null then '' else ', ' + FK11.PKCOLUMN_NAME end

+ case when FK12.PKCOLUMN_NAME is null then '' else ', ' + FK12.PKCOLUMN_NAME end

+ case when FK13.PKCOLUMN_NAME is null then '' else ', ' + FK13.PKCOLUMN_NAME end

+ case when FK14.PKCOLUMN_NAME is null then '' else ', ' + FK14.PKCOLUMN_NAME end

+ case when FK15.PKCOLUMN_NAME is null then '' else ', ' + FK15.PKCOLUMN_NAME end

+ case when FK16.PKCOLUMN_NAME is null then '' else ', ' + FK16.PKCOLUMN_NAME end

+ case when FK17.PKCOLUMN_NAME is null then '' else ', ' + FK17.PKCOLUMN_NAME end

+ case when FK18.PKCOLUMN_NAME is null then '' else ', ' + FK18.PKCOLUMN_NAME end

+ ' ) ' + char(13) + 'GO '

from #tmpFKeys FK1

left join #tmpFKeys FK2

on FK1.FK_NAME = FK2.FK_NAME and FK1.KEY_SEQ = 1 and FK2.KEY_SEQ = 2

left join #tmpFKeys FK3

on FK1.FK_NAME = FK3.FK_NAME and FK1.KEY_SEQ = 1 and FK3.KEY_SEQ = 3

left join #tmpFKeys FK4

on FK1.FK_NAME = FK4.FK_NAME and FK1.KEY_SEQ = 1 and FK4.KEY_SEQ = 4

left join #tmpFKeys FK5

on FK1.FK_NAME = FK5.FK_NAME and FK1.KEY_SEQ = 1 and FK5.KEY_SEQ = 5

left join #tmpFKeys FK6

on FK1.FK_NAME = FK6.FK_NAME and FK1.KEY_SEQ = 1 and FK6.KEY_SEQ = 6

left join #tmpFKeys FK7

on FK1.FK_NAME = FK7.FK_NAME and FK1.KEY_SEQ = 1 and FK7.KEY_SEQ = 7

left join #tmpFKeys FK8

on FK1.FK_NAME = FK8.FK_NAME and FK1.KEY_SEQ = 1 and FK8.KEY_SEQ = 8

left join #tmpFKeys FK9

on FK1.FK_NAME = FK9.FK_NAME and FK1.KEY_SEQ = 1 and FK9.KEY_SEQ = 9

left join #tmpFKeys FK10

on FK1.FK_NAME = FK10.FK_NAME and FK1.KEY_SEQ = 1 and FK10.KEY_SEQ = 10

left join #tmpFKeys FK11

on FK1.FK_NAME = FK11.FK_NAME and FK1.KEY_SEQ = 1 and FK11.KEY_SEQ = 11

left join #tmpFKeys FK12

on FK1.FK_NAME = FK12.FK_NAME and FK1.KEY_SEQ = 1 and FK12.KEY_SEQ = 12

left join #tmpFKeys FK13

on FK1.FK_NAME = FK13.FK_NAME and FK1.KEY_SEQ = 1 and FK13.KEY_SEQ = 13

left join #tmpFKeys FK14

on FK1.FK_NAME = FK14.FK_NAME and FK1.KEY_SEQ = 1 and FK14.KEY_SEQ = 14

left join #tmpFKeys FK15

on FK1.FK_NAME = FK15.FK_NAME and FK1.KEY_SEQ = 1 and FK15.KEY_SEQ = 15

left join #tmpFKeys FK16

on FK1.FK_NAME = FK16.FK_NAME and FK1.KEY_SEQ = 1 and FK16.KEY_SEQ = 16

left join #tmpFKeys FK17

on FK1.FK_NAME = FK17.FK_NAME and FK1.KEY_SEQ = 1 and FK17.KEY_SEQ = 17

left join #tmpFKeys FK18

on FK1.FK_NAME = FK18.FK_NAME and FK1.KEY_SEQ = 1 and FK18.KEY_SEQ = 18

where FK1.KEY_SEQ = 1

order by FK1.FKTABLE_OWNER, FK1.FKTABLE_NAME, FK1.FK_NAME

print '-- Create FK-Indexes'

print '-- keep FK column-ordinal / order equal to PK column-ordinal / order (asc/desc)'

select 'Create index [XFK_' + FK1.FK_NAME + '] on [' + FK1.FKTABLE_OWNER + '].[' + FK1.FKTABLE_NAME + '] ( '+