How to determine ASC/DESC of a column in an index?

(SQL Server 2000, SP3a)
Hello all!
Is there any way to programmatically determine the ASC/DESC nature of a column in an
index?
For example, if I declare an index to be:
create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
Thanks for any help you can provide!
John PetersonAfter a bit of fiddling around, the data *appears* to be in the [dbo].[sysindexes].[keys]
column. However, I'm not sure how to interpret this data. :-(
From this link:
http://sybooks.sybase.com/onlinebooks/group-asarc/asg1192e/asefun92/@.Generic__BookTextView/22111
I wish that SQL Server had a INDEX_COLORDER() built-in function. ;-)
Any additional thoughts?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:ei2YX6LAEHA.1036@.TK2MSFTNGP10.phx.gbl...
> (SQL Server 2000, SP3a)
> Hello all!
> Is there any way to programmatically determine the ASC/DESC nature of a column in an
> index?
> For example, if I declare an index to be:
> create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
> I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
> Thanks for any help you can provide!
> John Peterson
>|||sp_helpindex will return this information. The index_keys
column returns the index keys - descending has a minus sign
following the column name.
The INDEXKEY_PROPERTY function can be used to determine if
an index key is descending or ascending. Sysindexkeys system
table can help you in determining the key_id.
In SQL-DMO, you can use the GetIndexedColumnDESC method of
the Index2 object to determine if a column in an index is
descending.
-Sue
On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
<j0hnp@.comcast.net> wrote:
>(SQL Server 2000, SP3a)
>Hello all!
>Is there any way to programmatically determine the ASC/DESC nature of a column in an
>index?
>For example, if I declare an index to be:
>create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
>I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
>Thanks for any help you can provide!
>John Peterson
>|||Thanks, Sue! That's *exactly* what I was looking for! I cobbled something based on the
31st byte of the 32 byte chunks in the sysindexes.keys, but it was rife with disaster.
(But seemed to work!)
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:r3ja40lp2nde0h8pbf1fjc92oqfkc252de@.4ax.com...
> sp_helpindex will return this information. The index_keys
> column returns the index keys - descending has a minus sign
> following the column name.
> The INDEXKEY_PROPERTY function can be used to determine if
> an index key is descending or ascending. Sysindexkeys system
> table can help you in determining the key_id.
> In SQL-DMO, you can use the GetIndexedColumnDESC method of
> the Index2 object to determine if a column in an index is
> descending.
> -Sue
> On Tue, 2 Mar 2004 18:26:37 -0700, "John Peterson"
> <j0hnp@.comcast.net> wrote:
> >(SQL Server 2000, SP3a)
> >
> >Hello all!
> >
> >Is there any way to programmatically determine the ASC/DESC nature of a column in an
> >index?
> >
> >For example, if I declare an index to be:
> >
> >create unique clustered index [Test] on [MyTable](Field1 desc, Field2)
> >
> >I'd like to determine, somehow, that Field1 is declared with the DESC keyword.
> >
> >Thanks for any help you can provide!
> >
> >John Peterson
> >
>