script indexes

I have a monthly process where I replace all the data in certain tables. I
truncate the tables then DTS the new data in from another SQL server. I've
found that it's much faster to drop all the indexes on my tables and then
rebuild them at the end.
I'm looking to automate this process. It's easy to drop all the indexes in
a cursor - that's not really my issue. I'm currently using a vb app that I
wrote that uses DSO to generate a text file with the create index
statements, but it's not overly efficient because I don't want it to include
all tables - which it does. I've thought it might be more efficient to
query the sysindexes tables to find my indexes but I haven't found a great
way to save this information prior to dropping the indexes. I have no
objections to creating a table to store this information in.
I'm wondering if anyone has a suggestion for this?
Thanks in advance,
AndreBy DSO, do you mean SQL-DMO? If so, then why don't you iterate through
the collection of tables in your VB app, and only script out the
indexes for the tables of interest? You can do this in an ActiveX task
in the DTS package, saving the script to a global variable, and then
recreate the indexes using another ActiveX package to execute the SQL
statements.
I guess it sounds like you have all of the right elements to do what
you want to do; I'm not sure what the roadblock is.
Stu|||Yes, I do mean DMO - my mistake. You'll have to forgive me as I'm just a
rookie programmer and don't know DMO thoroughly. I don't know how to loop
through and get only the tables I'm interested in. This is a snippet of the
code I'm using to get my indexes:
For Each View In svr.Databases("MyDb").Tables
If InStr(View.Name, "sys") = 0 Then ' exclude system tables
Err.Clear
' add index info to text file
Print #1, View.Script(SQLDMOScript_Indexes)
End If
How would I modify it to get the indexes for specific tables? And how would
I store this in a global var?
Thanks for your help.
Andre|||First, I'd recommend that you get a book on DTS programming; there are
a couple of good ones out there. They'll help you get a grip on using
global variables, etc.
Second, here's a quick stab at some VBScript that may point you in the
right direction. If the tables are few, you can get by with SELECT
CASE. If you have a great deal of tables, you'll probably want to
write a function to look up the table name from an array.
Dim sql
SET SQLServer = CreateObject("SQLDMO.SqlServer")
SET Database = CreateObject("SQLDMO.Database")
SET Table = CreateObject("SQLDMO.Table")
SET Index = CreateObject("SQLDMO.Index")
SQLServer.LoginSecure = TRUE
SQLServer.Connect ServerName
For Each Database in SQLServer.Databases
If Database.SystemObject = False Then
Set filetxt = filesys.CreateTextFile(FilePAth & ServerName & "\" &
Database.Name & ".sql", True)
sql = Database.Script
sql = sql & CHAR(10) & "USE " & Database.Name & CHAR(10)
For each Table in Database.Tables
SELECT CASE Table.Name
CASE "Table1 you want"
For each Index in Table.Indexes
sql = sql & Index.script(532676612)
Next
CASE "Table You don't want"
END SELECT
Next
End if
Next
SQLServer.Disconnect
SET Index = NOTHING
SET Table = Nothing
SET Database = NOTHING
SET SQLServer = NOTHING
HTH,
Stu|||Hi Stu
I'm just now making my way back to this project and I seem to remember that
you had responded to this thread of mine with some example code. However I
don't see it anymore. I'm wondering if you could re-post it here?
Thanks very much.
Andre|||http://groups.google.com/group/micr...218b84be5a282a6
should take you to it. If not, let me know.|||Awesome, thanks Stu!
Andre