Dropping / Recreating Indexes

All -
You know when you generate the SQL script for a stored procedure, you get
that initial script at the top that first checks for the procedure in
sysobjects and drops it if it is found?
I want to do the same thing with indexes. I want to create a script that
will first delete a given index if its found and then create a new one.
How would you go about do this?
Thanks,
- Mattif exists (select * from dbo.sysindexes where name = 'IX_index_01' and id =object_id('dbo.tableA'))
drop index dbo.tableA.IX_index_01
GO
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||All -
Sorry about that. I found the DROP INDEX syntax.
Now it looks like the only problem is that if the index is a primary index,
you can't drop it. You have to drop the table, create the table, and then
re-create the index.
Does this sound right?
Thanks,
- Matt
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> All -
> You know when you generate the SQL script for a stored procedure, you get
> that initial script at the top that first checks for the procedure in
> sysobjects and drops it if it is found?
> I want to do the same thing with indexes. I want to create a script that
> will first delete a given index if its found and then create a new one.
> How would you go about do this?
> Thanks,
> - Matt
>
>|||Primary Key indexes are created as part of the Primary Key
Constraint. You cannot directly drop the index, but
instead you drop it by dropping the Primary Key
Constraint. So you don't have to drop the table. You must,
however, first drop any Foreign Key Constraints that
reference the PK.
HTH
Vern
>--Original Message--
>All -
>Sorry about that. I found the DROP INDEX syntax.
>Now it looks like the only problem is that if the index
is a primary index,
>you can't drop it. You have to drop the table, create
the table, and then
>re-create the index.
>Does this sound right?
>Thanks,
>- Matt
>
>"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
>news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
>> All -
>> You know when you generate the SQL script for a stored
procedure, you get
>> that initial script at the top that first checks for
the procedure in
>> sysobjects and drops it if it is found?
>> I want to do the same thing with indexes. I want to
create a script that
>> will first delete a given index if its found and then
create a new one.
>> How would you go about do this?
>> Thanks,
>> - Matt
>>
>
>.
>|||Hi Matt
No, you certainly don't have to drop the whole table just to drop an index!
If an index supports a primary key constraint, you have to drop the
constraint, and that will automatically drop the index that supports the
constraint.
You can then readd the constraint.
sp_helpconstraint will show you the constraint names
to drop and re-add the constraint use:
ALTER TABLE ... DROP CONSTRAINT
ALTER TABLE ... ADD CONSTRAINT
Full details are in Books Online
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>|||What's the reason behind dropping and recreating the index? If the reason
is to rebuild it (remove fragmentation), then use DBCC DBREINDEX. This will
rebuild your index and you don't need to deal with dropping constraints.
Gail Erickson [MSFT]
SQL Server User Education
This posting is provided "AS IS" with no warranties, and confers no rights.
"Matthew Sajdera" <sajdera@.pcts.com> wrote in message
news:e2aLycpkDHA.2432@.TK2MSFTNGP10.phx.gbl...
> All -
> Sorry about that. I found the DROP INDEX syntax.
> Now it looks like the only problem is that if the index is a primary
index,
> you can't drop it. You have to drop the table, create the table, and then
> re-create the index.
> Does this sound right?
> Thanks,
> - Matt
>
> "Matthew Sajdera" <sajdera@.pcts.com> wrote in message
> news:eeA8tWpkDHA.360@.TK2MSFTNGP12.phx.gbl...
> > All -
> >
> > You know when you generate the SQL script for a stored procedure, you
get
> > that initial script at the top that first checks for the procedure in
> > sysobjects and drops it if it is found?
> >
> > I want to do the same thing with indexes. I want to create a script
that
> > will first delete a given index if its found and then create a new one.
> >
> > How would you go about do this?
> >
> > Thanks,
> >
> > - Matt
> >
> >
> >
>