topic Re: Show indeces of Explicit database table. in SAS Programming

Show indeces of Explicit database table.

tomrvincent — Fri, 14 Feb 2020 14:56:49 GMT

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'.

Re: Show indeces of Explicit database table.

Reeza — Fri, 14 Feb 2020 16:03:33 GMT

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'.

Re: Show indeces of Explicit database table.

tomrvincent — Fri, 14 Feb 2020 16:37:20 GMT

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.

Re: Show indeces of Explicit database table.

Reeza — Fri, 14 Feb 2020 16:49:17 GMT

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

Re: Show indeces of Explicit database table.

tomrvincent — Fri, 14 Feb 2020 16:55:24 GMT

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.

Re: Show indeces of Explicit database table.

Tom — Fri, 14 Feb 2020 16:56:21 GMT

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?

Re: Show indeces of Explicit database table.

Reeza — Fri, 14 Feb 2020 16:57:54 GMT

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

Re: Show indeces of Explicit database table.

tomrvincent — Fri, 14 Feb 2020 17:23:59 GMT

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

Re: Show indeces of Explicit database table.

tomrvincent — Fri, 14 Feb 2020 17:24:59 GMT

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'.

Re: Show indeces of Explicit database table.

Tom — Fri, 14 Feb 2020 17:30:14 GMT

Are you using ODBC driver to connect to Sybase?

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

Re: Show indeces of Explicit database table.

tomrvincent — Fri, 14 Feb 2020 17:35:41 GMT

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