I am reading the index keys through a query and not using sp_helpindex
because I need to make a join on the query.
How can I know if the index column is in descending order
the query i am using is the following:
select sysobjects.name as TableName
, sysindexes.name as IndexName
, sysindexkeys.keyno as Position
, syscolumns.name as FieldName
from sysindexkeys
left outer join sysobjects on sysindexkeys.id = sysobjects.id
left outer join sysindexes on sysindexkeys.id = sysindexes.id and
sysindexkeys.indid = sysindexes.indid
left outer join syscolumns on sysindexkeys.id = syscolumns.id and
sysindexkeys.colid = syscolumns.colid
order by sysobjects.name, sysindexes.name, sysindexkeys.keynoHi
Although you are not using sp_helpindex, you may have noticed that decending
keys are notated by a '(-)' next to the key columns when displaying, checkin
g
out the source for sp_helpindex you will see:
if (indexkey_property(@.objid, @.indid, 1, 'isdescending') = 1)
select @.keys = @.keys + '(-)'
Therefore this can be incorporated in your statement:
select o.name as TableName
, i.name as IndexName
, k.keyno as Position
, c.name as FieldName
, CASE WHEN indexkey_property(o.id, i.indid, 1, 'isdescending') = 1) THEN
'Descending'
ELSE 'Ascending'
END AS Direction
from sysindexkeys k
left join sysobjects o on k.id = o.id
left join sysindexes i on k.id = i.id and k.indid = i.indid
left outer join syscolumns c on k.id = c.id and k.colid = c.colid
order by o.name, i.name, k.keyno
John
"Nadim Wakim - Lebanon" wrote:
> I am reading the index keys through a query and not using sp_helpindex
> because I need to make a join on the query.
> How can I know if the index column is in descending order
>
> the query i am using is the following:
> select sysobjects.name as TableName
> , sysindexes.name as IndexName
> , sysindexkeys.keyno as Position
> , syscolumns.name as FieldName
> from sysindexkeys
> left outer join sysobjects on sysindexkeys.id = sysobjects.id
> left outer join sysindexes on sysindexkeys.id = sysindexes.id and
> sysindexkeys.indid = sysindexes.indid
> left outer join syscolumns on sysindexkeys.id = syscolumns.id and
> sysindexkeys.colid = syscolumns.colid
> order by sysobjects.name, sysindexes.name, sysindexkeys.keyno
>
>|||Dear John, although indexkey_property is working in the current db, i need
to use it to query from another db in order to compare indexes between 2
dbs.
if i use:
Select indexkey_property(o.id, i.indid, 1, 'isdescending')
from db1.dbo.sysobjects o
left outer join sysobjects current on ......
, the function returns NULL because it is looking for the id in the current
database.
if i try to use db1.dbo.indexkey_property(), i get an error.
I am trying to compare the indexes structure between a template database and
the database i am connecting to in a query.
"John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
news:9498A7E0-D40B-4D13-A1C2-751EB0B03B8F@.microsoft.com...
> Hi
> Although you are not using sp_helpindex, you may have noticed that
> decending
> keys are notated by a '(-)' next to the key columns when displaying,
> checking
> out the source for sp_helpindex you will see:
> if (indexkey_property(@.objid, @.indid, 1, 'isdescending') = 1)
> select @.keys = @.keys + '(-)'
> Therefore this can be incorporated in your statement:
> select o.name as TableName
> , i.name as IndexName
> , k.keyno as Position
> , c.name as FieldName
> , CASE WHEN indexkey_property(o.id, i.indid, 1, 'isdescending') = 1) THEN
> 'Descending'
> ELSE 'Ascending'
> END AS Direction
> from sysindexkeys k
> left join sysobjects o on k.id = o.id
> left join sysindexes i on k.id = i.id and k.indid = i.indid
> left outer join syscolumns c on k.id = c.id and k.colid = c.colid
> order by o.name, i.name, k.keyno
>
> John
> "Nadim Wakim - Lebanon" wrote:
>|||Instead of reinventing the wheel there are lots of inexpensive 3rd party
products out there that do these comparisons for you.
http://www.aspfaq.com/show.asp?id=2209
Some of them (I know Red-Gate does) come with an api to allow you to do
these comparisons programatically.
Andrew J. Kelly SQL MVP
"Nadim Wakim" <nadimlb@.cyberia.net.lb> wrote in message
news:e35REvNEFHA.1264@.TK2MSFTNGP12.phx.gbl...
> Dear John, although indexkey_property is working in the current db, i need
> to use it to query from another db in order to compare indexes between 2
> dbs.
> if i use:
> Select indexkey_property(o.id, i.indid, 1, 'isdescending')
> from db1.dbo.sysobjects o
> left outer join sysobjects current on ......
> , the function returns NULL because it is looking for the id in the
> current database.
> if i try to use db1.dbo.indexkey_property(), i get an error.
> I am trying to compare the indexes structure between a template database
> and the database i am connecting to in a query.
>
>
> "John Bell" <JohnBell@.discussions.microsoft.com> wrote in message
> news:9498A7E0-D40B-4D13-A1C2-751EB0B03B8F@.microsoft.com...
>|||Dear Andrew, i tried 'Red gate' it's very efficient but it doesn't work in
case or replication.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:eFH6llQEFHA.732@.TK2MSFTNGP12.phx.gbl...
> Instead of reinventing the wheel there are lots of inexpensive 3rd party
> products out there that do these comparisons for you.
> http://www.aspfaq.com/show.asp?id=2209
> Some of them (I know Red-Gate does) come with an api to allow you to do
> these comparisons programatically.
>
> --
> Andrew J. Kelly SQL MVP
>
> "Nadim Wakim" <nadimlb@.cyberia.net.lb> wrote in message
> news:e35REvNEFHA.1264@.TK2MSFTNGP12.phx.gbl...
>|||Hi
You can put your results into a table and then process them
USE TEMPDB
CREATE TABLE [IndexTypes] (
[DBName] [nvarchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[name] [sysname] NOT NULL ,
[id] [int] NOT NULL ,
[indid] [smallint] NOT NULL ,
[Direction] [int] NULL
)
GO
USE DB1
INSERT INTO tempdb..IndexTypes
Select db_name() as DBName, o.name, o.id, i.indid,
indexkey_property(o.id, i.indid, 1, 'isdescending') as Direction
from sysobjects o
JOIN sysindexes i ON o.id = i.id
USE DB2
INSERT INTO tempdb..IndexTypes
Select db_name() as DBName, o.name, o.id, i.indid,
indexkey_property(o.id, i.indid, 1, 'isdescending') as Direction
from sysobjects o
JOIN sysindexes i ON o.id = i.id
SELECT * FROM tempdb..IndexTypes
where name = 'MyTable2'
John
Nadim Wakim wrote:
> Dear Andrew, i tried 'Red gate' it's very efficient but it doesn't
work in
> case or replication.
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:eFH6llQEFHA.732@.TK2MSFTNGP12.phx.gbl...
party
to do
db, i
indexes
the
database
that
displaying,
1)
sp_helpindex
and
and
