CODEWITHSUNDEEP
sqlindexingsybase
user3041581
user3041581

Reputation: 91

How can find out if an index exists on a #table in Sysbase?

I'm working in Sybase 15 SQL, in stored procedures. I want to identify the indices on a temporary #table. The usual techniques work on a permanent table but don't work on a temp table:

--look for an index on a temporary table

create table #T1( duff int)

create index idx99 on #T1(duff)

select * from sysindexes where name = 'idx99' --returns null rows !

--Look for an index on a permanent table

create table T1( duff int)

create index idx99 on T1(duff)

select * from sysindexes where name = 'idx99' --returns a row. OK for perm table.

Any ideas?

Bob

Upvotes: 2

Views: 3099

Answers (1)

Mike Gardner
Mike Gardner

Reputation: 6651

For #tables, the query for finding the indexes is the same as a normal table, but it needs to be executed against the tempdb (or the temp database for your session if it's not default).

SELECT o.name, i.name
FROM tempdb..sysobjects o, tempdb..sysindexes i
WHERE o.id = i.id
AND o.name like "#T1%"
AND i.name like "idx99"

Upvotes: 5

Related Questions