How to avoid creating duplicate indexes on the same column

15 Mar 2007 9:10 PM

Sarah Bram

Before creating an index on a table column, how to check if that column
was already indexed (with a different index name)?

I have to do this in SQL Script.

Thanks,
Sarah

16 Mar 2007 12:57 AM

Hari Prasad

Hello,

Just run SP_HELP or SP_Helpindex on that table and see. if the specific
column is already indexed

Thanks
Hari

Show quoteHide quote

"Sarah Bram" <sarahb***@hotmail.com> wrote in message
news:OUCqLX0ZHHA.4220@TK2MSFTNGP03.phx.gbl...
> Before creating an index on a table column, how to check if that column
> was already indexed (with a different index name)?
>
> I have to do this in SQL Script.
>
> Thanks,
> Sarah

16 Mar 2007 1:30 AM

Sarah Bram

Hari Prasad wrote:

> Just run SP_HELP or SP_Helpindex on that table and see. if the specific
> column is already indexed

Thanks for the reply but I have to do the checking through SQL Script
that creates indexes. Not by verifying it manually.

16 Mar 2007 4:54 PM

Gert-Jan Strik

That is still possible with the use of the system views that expose the
index columns.

However, in that case it would probably be easier to have consistent
index naming, and simply check for the existance of the named index.

Gert-Jan

Sarah Bram wrote:
Show quoteHide quote

>
> Hari Prasad wrote:
> > Just run SP_HELP or SP_Helpindex on that table and see. if the specific
> > column is already indexed
>
> Thanks for the reply but I have to do the checking through SQL Script
> that creates indexes. Not by verifying it manually.

16 Mar 2007 7:18 PM

Sarah Bram

Gert-Jan Strik wrote:

> That is still possible with the use of the system views that expose the
> index columns.

Can I see an example?

> However, in that case it would probably be easier to have consistent
> index naming, and simply check for the existance of the named index.

I sure do that for what I do now,
but it's a 10 year old product with several hundred clients with no
consistent naming.

Thanks,
Sarah

16 Mar 2007 7:26 PM

Gert-Jan Strik

If you are running SQL Server 2005, then have a look at the virtual
views sys.sysindexes and sys.sysindexkeys

If you are on an earlier version, check out the tables sysindexes and
sysindexkeys.

I am sure you should be able to google some examples...

Gert-Jan

Sarah Bram wrote:
Show quoteHide quote

>
> Gert-Jan Strik wrote:
> > That is still possible with the use of the system views that expose the
> > index columns.
>
> Can I see an example?
>
> > However, in that case it would probably be easier to have consistent
> > index naming, and simply check for the existance of the named index.
>
> I sure do that for what I do now,
> but it's a 10 year old product with several hundred clients with no
> consistent naming.
>
> Thanks,
> Sarah