2

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

Max
  • 12,622
  • 16
  • 73
  • 101
user3041581
  • 91
  • 3
  • 4
  • This may suit well here, I think: http://stackoverflow.com/questions/1468183/how-i-can-find-the-list-of-sybase-indexes-for-a-given-database – LWNirvana Jan 17 '14 at 14:32
  • @LWNirvana it's close. The main difference is that temp tables keep their information in the temp database. – Mike Gardner Jan 17 '14 at 16:02
  • @user3041581 Hi, I noticed you haven't accepted my answer yet, and just wanted to follow up. – Mike Gardner Jan 30 '14 at 20:17

1 Answers1

5

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"
Mike Gardner
  • 6,611
  • 5
  • 24
  • 34