Show indeces of Explicit database table.

[tomrvincent]

Anybody know how to show the indeces of a connected database table?

Proc contents doesn't show them, so I tried to pass a describe to sybase...can't get it to work...tried a number of ways.

execute(describe table ' MEMBER ') by sybase;

SYBASE_43: Executed: on connection 0
describe table ' MEMBER '

SYBASE: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
ERROR: Error executing statement. Error Code: -156 Incorrect syntax near the keyword 'table'.

[Reeza]

You tried explicit SQL Pass through with the describe statement?

What about querying from sp_tables or sp_columns directly?

select * from mysybase.sp_tables

---

@tomrvincent wrote:

Anybody know how to show the indeces of a connected database table?

Proc contents doesn't show them, so I tried to pass a describe to sybase...can't get it to work...tried a number of ways.

execute(describe table ' MEMBER ') by sybase;

SYBASE_43: Executed: on connection 0
describe table ' MEMBER '

SYBASE: 0 row(s) affected by INSERT/UPDATE/DELETE or other statement.
ERROR: Error executing statement. Error Code: -156 Incorrect syntax near the keyword 'table'.

---

[tomrvincent]

Nope...that doesn't work either.  Tried explicit and implicit.  I can't find any examples that work!

select * from connection to sybase(select * from sp_tables);

SYBASE_49: Prepared: on connection 0

select * from sp_tables

ERROR: Error Code: -208  sp_tables not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help 

       may produce lots of output).

Implicit error:

NOTE: Libref FOO was successfully assigned as follows:
Engine: SYBASE
Physical Name: RPT_XEG_2
29
30 select * from foo.sp_tables;

SYBASE_56: Prepared: on connection 1
SELECT * FROM dbo.sp_tables

ERROR: File FOO.sp_tables.DATA does not exist.

[Reeza]

What works in sybase directly?

I would have expected the following to work from pass through. Do you need to specify a schema?

select * from dbo.sp_tables

[tomrvincent]

No idea. I don't have any Windows software that connects to Sybase...just SAS on Linux.

I actually do specify a schema of dbo in my connection string.

Sybase dox say 'DESCRIBE INDEX FOR TABLE Customers' should work but I get errors with every variant of that I've tried.

[Reeza]

Can you post the full code you're using? With just a select * from sp_tables?

[Tom]

Why did you give the DESCRIBE TABLE command a string instead of a table name?

How does the DESCRIBE TABLE command return its results?  Does it look like a table?  Perhaps you want use this syntax instead.

select * from connection to sybase 
  (describe table MEMBER) 
;

That syntax works well for many Teradata commands.

Or perhaps there is some function in Sybase you can wrap around the describe table command to have its output converted to the same type of output that a normal SELECT statement would return?

[tomrvincent · Accepted solution]

Got it to work...using system proc sp_helpindex

29 proc sql;
33 select * from connection to foo( sp_helpindex CLAIM);

SYBASE_70: Prepared: on connection 3
sp_helpindex CLAIM

TRACE: sydesc()
SYBASE: Cursor closed.

Found that proc on http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc36273_1251/html/sprocs/X95841.htm

[Tom]

Are you using ODBC driver to connect to Sybase?

https://v8doc.sas.com/sashtml/accdb/z0421269.htm

[tomrvincent]

It's Linux, so yes'-ish'.

[tomrvincent]

tried that...got this error:

37 select * from connection to foo
38 (describe table CLAIM)
39 ;

SYBASE_69: Prepared: on connection 3
describe table CLAIM

ERROR: Error Code: -156 Incorrect syntax near the keyword 'table'.