September 24, 2003 at 10:41 am
Does anyone have a script to automatically create drop and create index statements for a whole database?
THANKS!
Francis
-----------------
SQLRanger.com
September 24, 2003 at 1:01 pm
A generic script to do this could really be detrimental, ie: how would you go about making sure you have created the indexes for the best performance you need? This is something that is specific for each different database. Creating a script to drop the indexes is fairly straight forward using the sysobjects and sysindexes tables. But I wouldn't want to do something like this to create new indexes.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Edited by - gljjr on 09/24/2003 1:02:09 PM
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 24, 2003 at 1:18 pm
Easy enough with SQL-DMO...
Sub DropCreateIndexes(Server As String, DB As String)
Dim objServer As New SQLDMO.SQLServer
Dim objDatabase As New SQLDMO.Database
Dim objIndex As New SQLDMO.Index
Dim objTable As New SQLDMO.Table
Const Path As String = "C:\Temp\"
objServer.LoginSecure = True
objServer.Connect Server
Set objDatabase = objServer.Databases(DB)
For Each objTable In objDatabase.Tables
For Each objIndex In objTable.Indexes
objIndex.Script _
ScriptType:=SQLDMOScript_Default _
Or SQLDMOScript_OwnerQualify _
Or SQLDMOScript_Drops _
Or SQLDMOScript_AppendToFile, _
scriptfilepath:=Path & "Indexes_" & objDatabase.Name & ".sql"
Next
Next
End Sub
--Jonathan
Edited by - jonathan on 09/24/2003 1:22:23 PM
--Jonathan
September 25, 2003 at 3:38 am
hi!
as long as you're not afraid from playing around with T-SQL scripts and generating SQL from there, you could use the following as a basis for recreating normal indexes (with a bit more effort also unique and primary keys):
http://www.sqlservercentral.com/scripts/contributions/246.asp
but if you just want to update your index structure (eg. when updating statistics manually) then you should use DBCC REINDEX (careful!) or DBCC INDEXDEFRAG (for just cleaning up space used by your indexes).
best regards,
chris.
September 25, 2003 at 8:36 am
Won't the easiest way to accomplish this task be to include it in your database maintenance plan? Optimization - Reorganize data & index pages.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply