Need help scripting the Create/Drop index statement for all indexes within a database

Question

Post reply

Need help scripting the Create/Drop index statement for all indexes within a database

John-79567

SSCommitted

Points: 1892
More actions
June 6, 2005 at 2:40 am

#86898

I tried using the ent mgr script tool, but it appears to drop/create the tables as well. this made me a little nervous.

Viewing 6 posts - 1 through 5 (of 5 total)

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

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

If you just want the indexes, PKs, etc... Just uncheck the DROP\CREATE tables check boxes

Good Hunting!

AJ Ahrens

webmaster@kritter.net

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

Where are those check boxes? I can only find CREAT / DROP < object >

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

John-79567 SSCommitted Points: 1892 More actions · Answer 3

Thanks for the help. I was able to script the create index event. Now, Do i need to add a line to drop each index if it exist?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

It's always a good idea to have a check before a drop. That way you won't have errors when you run the script.

Dezman SSC-Addicted Points: 492 More actions · Answer 5

I created a script that loops thru user tables checking for index existing on table.

if index(s) found - (as Remi noted ), it runs a reindex routine, otherwise, skips table.

partial snippet rebuild with 70 fill factor:

BEGIN

PRINT 'Executing DBCC db reINDEX (' + RTRIM(@tblname)+ ', '''', 70)'

SELECT @execstr = 'DBCC DBREINDEX (' + RTRIM(@tblname) + ', '''', 70)'

EXEC (@execstr)

FETCH NEXT

My understanding of DBCC DBREINDEX on a table name is where it will drop and recreate indexes. more parameters, more control.

This level and detail was fine for me, and allos the drop and create of all user table indexes in my database(s). ( as far as I know...)

While BOL says to abandon DBREINDEX for DROP and CREATE... it also states that DBREINDEX is automatically atomic, where DROP and CREATE need transaction wrappers to achieve the same... ( more work...)

enjoy