How to find index details on Temporary tables in SYBASE
Assuming this is Sybase ASE ...



I would probably pull the create index out to a different place in your code, eg:


create/select-into &lt;table&gt;, create index, looping/insert, update statistics &lt;table&gt;, run queries


or


create/select-into &lt;table&gt;, looping/insert, create index, (optionally update statistics &lt;table&gt; - depends on your query requirements), run queries




If you insist on throwing the create index into the middle of your looping/insert construct (I wouldn't recommend this), consider ...


to query the system tables you need to know which tempdb your session is assigned to (assuming your ASE is configured with multiple tempdb's), and then query the system tables in that particular tempdb (eg, build a dynamic query)


or


you can use some of the built-in system functions to run some tests


Your current tempdb's database id can be found in @@tempdbid (or you can call tempdb_id()).  You can then call db_name(@@tempdbid) (or db_name(tempdb_id())) to get your tempdb name.  From here you can build your dynamic query against sysindexes. [Plenty of posts on dynamic ASE queries so I'm not going to cover that here.]

Instead of building a dynamic query I'd probably opt for a (relatively) simple function-based test, eg:

NOTE: Assuming no other indexes on the table ... the index id should be 1 or 2 ... depending on the table's locking scheme and the type of index (clustered/non-clustered).

if  index_name(@@tempdbid,object_id(#TABLE_X),1) is NULL
and index_name(@@tempdbid,object_id(#TABLE_X),2) is NULL
begin
    create index ...
end


Regardless of whether you build/run a dynamic query, or use the system functions to test for the existence of the index, I would highly recommend you re-think your code design.  Do you really, Really, REALLY want to add the extra overhead for a dynamic-query/function-call to every pass through your looping/insert construct?