sql server programming: 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,

Andre

By 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