Finding Indexes for a table

I want to delete an index for a specific table. Normally I would use:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'name_of_index')
DROP INDEX table.name_of_index
where name_of_index is called "ID" (I know, I know, but not my idea)
Unfortunately, the moron who designed the database decided to call all
the indexes of a particular type "ID" so there are hundreds of them in
sysindexes, making it impossible to figure out which one it is I want
to delete.
Does anyone have a handy bit of SQL which will pick out the specific
index of that name for a particular table.
S'okay I figured it out.
if exists (select * from sysindexes where name = 'name_of_index' and id
= object_id('table'))
drop index table.name_of_index
aaron@.castle-cadenza.demon.co.uk wrote:

> I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
|||I use sp_help tablename to get the name and the columns in an index.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<aaron@.castle-cadenza.demon.co.uk> wrote in message
news:1161168159.260136.117780@.f16g2000cwb.googlegr oups.com...
>I want to delete an index for a specific table. Normally I would use:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'name_of_index')
> DROP INDEX table.name_of_index
> where name_of_index is called "ID" (I know, I know, but not my idea)
> Unfortunately, the moron who designed the database decided to call all
> the indexes of a particular type "ID" so there are hundreds of them in
> sysindexes, making it impossible to figure out which one it is I want
> to delete.
> Does anyone have a handy bit of SQL which will pick out the specific
> index of that name for a particular table.
>

Find and drop a constraint before dropping the column

Hi all.
I want to check and find the name of the constraint and drop it before I
drop the column. Or, just drop the column without the errormessage that
there are depending objects. Any ideas?
Now I have to drop the column, look at the errormessage, copy/paste into a
drop constraint and then drop the column again.
Thanx all.
ALTER TABLE Tablename
DROP CONSTRAINT DF__Tablename__ColumnName__21D6CC45
GO
ALTER TABLE Tablename
DROP COLUMN ColumnName
goYou can use sp_help and sp_helpconstraint.
can view PK, FK,ID,CHECK etc.
"Geir Holme"?? ??? ??:

> Hi all.
> I want to check and find the name of the constraint and drop it before I
> drop the column. Or, just drop the column without the errormessage that
> there are depending objects. Any ideas?
> Now I have to drop the column, look at the errormessage, copy/paste into a
> drop constraint and then drop the column again.
> Thanx all.
>
> ALTER TABLE Tablename
> DROP CONSTRAINT DF__Tablename__ColumnName__21D6CC45
> GO
> ALTER TABLE Tablename
> DROP COLUMN ColumnName
> go
>
>