how check the existing index

Hello,
How can I check an existing index on a table?
I have found this:
IF EXISTS (SELECT name FROM sysindexes
WHERE name = 'my_ind_name')
DROP INDEX my_table_name.my_ind_name
My Problem is following:
1. What happens when this index is on an other table (e.g 'my_other_table')
and not on the original table ('my_table_name') Will I get an error
message? I would like to avoid this.
2. As far as I know it is not recommended to reffer to system objects
directly.
Instead of it I should use INFORMATION_SCHEMA. But how?
Thanks for any help
Danyi, AttilaTry,
if exists(
select
*
from
sysindexes
where
[id] = object_id('owner.table_name')
and [indid] between 1 and 254
and indexproperty([id], [name], 'IsStatistics') = 0
and indexproperty([id], [name], 'IsHypothetical') = 0
and [name] = 'index_name'
)
...
There is not an information_schema view to query about indexes.
AMB
"Danyi, Attila" wrote:

> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g 'my_other_table
')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>|||> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
You need to also filter on table:
IF EXISTS (SELECT * FROM sysindexes
WHERE name = 'my_ind_name' AND
OBJECT_NAME(id) = N'my_table_name')
DROP INDEX my_table_name.my_ind_name

> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
Indexes are not exposed via the INFORMATION_SCHEMA views because these are
implementation specific. It's ok to use documented system table columns and
functions to query meta data, although I prefer to use the
INFORMATION_SCHEMA views when possible.
Hope this helps.
Dan Guzman
SQL Server MVP
"Danyi, Attila" <DanyiAttila@.discussions.microsoft.com> wrote in message
news:4241A175-9CA7-4B83-903D-E2787BE3927C@.microsoft.com...
> Hello,
> How can I check an existing index on a table?
> I have found this:
> IF EXISTS (SELECT name FROM sysindexes
> WHERE name = 'my_ind_name')
> DROP INDEX my_table_name.my_ind_name
> My Problem is following:
> 1. What happens when this index is on an other table (e.g
> 'my_other_table')
> and not on the original table ('my_table_name') Will I get an error
> message? I would like to avoid this.
> 2. As far as I know it is not recommended to reffer to system objects
> directly.
> Instead of it I should use INFORMATION_SCHEMA. But how?
> Thanks for any help
> Danyi, Attila
>