there are constraints that have to be deleted first. So, I figure OK,
I'll just kill the constraints first, then the indexes will go away
with ease. NOT! Since there are two ways to do things, using
standard SQL and using the SQLDMO object, I tried both. Here
is where I am with the code right now:
'******************************
Dim server
Dim tbcnt
Dim idcnt
Dim kecnt
Dim i, j, k
Dim tblname
Dim keyname
Dim sql
Set server = CreateObject("SQLDMO.SQLServer")
server.Connect "Server", "sa", ""
i = "Northwind"
tbcnt = server.Databases(i).Tables.Count
For j = 1 to tbcnt
If server.Databases(i).Tables(j).TypeOf = 8 Then
tblname = server.Databases(i).Tables(j).Name
kecnt = server.Databases(i).Tables(j).Keys.Count
idcnt = server.Databases(i).Tables(j).Indexes.Count
For k = 1 to kecnt
keyname = server.Databases(i).Tables(j).Keys(k).Name
' was: server.Databases(i).Tables(j).Keys(k).Remove
sql = "USE " & i & vbCrLf & _
"ALTER TABLE " & tblname & vbCrLf & _
"DROP CONSTRAINT " & keyname
server.ExecuteImmediate(sql)
Next
For k = 1 to idcnt
server.Databases(i).Tables(j).Indexes(k).Remove
Next
End If
Next
'******************************
Now, Keys here are really constraints, so I tried this, using a
mixture of SQL and VB code, and I also tried it with all VB,
and all SQL as well. No matter what I try, I keep getting
'Constrain xxxx is being referenced by Foreign Key uuuu...etc.
and
'Could not drop constraint xxxx'
Does anyone know if this can be automated like this? If so,
How? I'm already past tearing my hair out, I hate to think
what happens next...
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%23d0XGCbQEHA.2132@.TK2MSFTNGP11.phx.gbl...
> So, run the first query, bring back the "DROP INDEX ...; GO; DROP INDEX;
> GO;" statements into a variable, and then execute that just like you
> executed the initial query.
> A
>
How about drop the database and re-create it?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Gary Morris" <gwmorris1@.hotpop.com> wrote in message
news:O8YoOieQEHA.3748@.TK2MSFTNGP09.phx.gbl...
> Would that it were so simple! I tried that method, no go because
> there are constraints that have to be deleted first. So, I figure OK,
> I'll just kill the constraints first, then the indexes will go away
> with ease. NOT! Since there are two ways to do things, using
> standard SQL and using the SQLDMO object, I tried both. Here
> is where I am with the code right now:
> '******************************
> Dim server
> Dim tbcnt
> Dim idcnt
> Dim kecnt
> Dim i, j, k
> Dim tblname
> Dim keyname
> Dim sql
> Set server = CreateObject("SQLDMO.SQLServer")
> server.Connect "Server", "sa", ""
> i = "Northwind"
> tbcnt = server.Databases(i).Tables.Count
> For j = 1 to tbcnt
> If server.Databases(i).Tables(j).TypeOf = 8 Then
> tblname = server.Databases(i).Tables(j).Name
> kecnt = server.Databases(i).Tables(j).Keys.Count
> idcnt = server.Databases(i).Tables(j).Indexes.Count
> For k = 1 to kecnt
> keyname = server.Databases(i).Tables(j).Keys(k).Name
> ' was: server.Databases(i).Tables(j).Keys(k).Remove
> sql = "USE " & i & vbCrLf & _
> "ALTER TABLE " & tblname & vbCrLf & _
> "DROP CONSTRAINT " & keyname
> server.ExecuteImmediate(sql)
> Next
> For k = 1 to idcnt
> server.Databases(i).Tables(j).Indexes(k).Remove
> Next
> End If
> Next
> '******************************
> Now, Keys here are really constraints, so I tried this, using a
> mixture of SQL and VB code, and I also tried it with all VB,
> and all SQL as well. No matter what I try, I keep getting
> 'Constrain xxxx is being referenced by Foreign Key uuuu...etc.
> and
> 'Could not drop constraint xxxx'
> Does anyone know if this can be automated like this? If so,
> How? I'm already past tearing my hair out, I hate to think
> what happens next...
> "Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
> news:%23d0XGCbQEHA.2132@.TK2MSFTNGP11.phx.gbl...
>
|||How did I know you would say that
Actually I had thought of that, but was dearly hoping
that I wouldn't have to resort to it. I just cannot
believe that SQL Server will not allow this in an easier
way. It's no problem manually, but programmatically
it just won't work. Eight hours working on this one
problem today..not very productive. Why can't it just
work like the Sybase version?
Well, I'll hunt around some just to satisfy myself that
some nerdy guru hasn't found a way around this
already, then I'll look into the alternative. What I'm
trying to accomplish is a script that will drop just the
indexes of ALL the user tables, OR just the indexes
on specified tables. I won't go into the reasons, that's
just the way it has to work. Now, I have considered
just writing the sql out for each table, parsing it to
remove the constraint/index parts, and then reimport
the table(s) back into the db, which would work, I
just consider it to be a dirty hack, and I should have
a nice clean, acceptable way to do it. This sucks...
"Aaron Bertrand [MVP]" <aaron@.TRASHaspfaq.com> wrote in message
news:e%23lGhGgQEHA.252@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> How about drop the database and re-create it?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
> "Gary Morris" <gwmorris1@.hotpop.com> wrote in message
> news:O8YoOieQEHA.3748@.TK2MSFTNGP09.phx.gbl...
INDEX;
>
sql
