From a Sybase Database, How to Get Table Description ( Field Names and Types)

From a Sybase Database, how I can get table description ( field names and types)?

Check sysobjects and syscolumns tables.

Here is a diagram of Sybase system tables.

List of all user tables:

SELECT * FROM sysobjects WHERE type = 'U'

You can change 'U' to other objects:

C – computed column
D – default
F – SQLJ function
L – log
N – partition condition
P – Transact-SQL or SQLJ procedure
PR – prepare objects (created by Dynamic SQL)
R – rule
RI – referential constraint
S – system table
TR – trigger
U – user table
V – view
XP – extended stored procedure

List of columns in a table:

SELECT sc.* 
FROM syscolumns sc
INNER JOIN sysobjects so ON sc.id = so.id
WHERE so.name = 'my_table_name'

How to get sybase table column name and its datatype and order by?

To extract types I am using such query:

SELECT syscolumns.name, systypes.name FROM sysobjects 
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON systypes.type = syscolumns.type AND systypes.usertype = syscolumns.usertype
WHERE sysobjects.name LIKE 'my_table'

How to see Table columns in sorted order in Sybase

select col.name from 
sysobjects tab 
inner join 
syscolumns col 
on tab.id = col.id 
where tab.type = 'U' 
and tab.name = 'TABLE_NAME' 
order by col.name

Sybase ASA database and listing columns with types

I think that sys.syscolumns is a view that is related to ASE compatibility. You probably want to join SYSTAB, and SYSTABCOL.

select t.creator, t.table_name, t.table_id, c.column_type, c.nulls, c.width, c.default
  from sys.systab t, sys.systabcol c
where t.table_id = c.table_id

You may have to also reference SYSIDX to get the primary key information.

References to the system tables and their structure can be found in the documentation. In most cases you can use the SQLAnywhere v10 documentation to find answers about Sybase ASA.

How can I get all column names matching a given string in Sybase?

Updated based on OPs additional comments re: question is for a Sybase IQ database.

I don't have a Sybase IQ database in front of me at the moment but we should be able to piece together a workable query based on IQ's system tables/views:

IQ system views
IQ system tables

The easier query will use the system view SYSCOLUMNS:

select  cname
from    SYS.SYSCOLUMNS
where   tname = '<table_name>'
and     cname like '%<pattern_to_match>%'

Or going against the system tables SYSTABLE and SYSCOLUMN:

select  c.column_name
from    SYS.SYSTABLE t
join    SYS.SYSCOLUMN c
on      t.table_id = c.table_id
where   t.table_name = '<table_name>'
and     c.column_name like '%<pattern_to_match>%'

NOTE: The Sybase ASE query (below) will probably also work since the referenced (ASE) system tables (sysobjects, syscolumns) also exist in SQL Anywhere/IQ products as a (partial) attempt to provide (ASE) T-SQL compatibility.

Assuming this is Sybase ASE then a quick join between sysobjects and syscolumns should suffice:

select  c.name
from    dbo.sysobjects o
join    dbo.syscolumns c
on      o.id = c.id
and     o.type in ('U','S')            -- 'U'ser or 'S'ystem table
where   o.name = '<table_name>'
and     c.name like '%<portion_of_column_name>%'

For example, let's say we want to find all columns in the sysobjects table where the column name contains the string 'trig':

select  c.name
from    dbo.sysobjects o
join    dbo.syscolumns c
on      o.id = c.id
and     o.type in ('U','S')
where   o.name = 'sysobjects'
and     c.name like '%trig%'
order by 1
go

 ----------
 deltrig
 instrig
 seltrig
 updtrig

Table Details in SQL Anywhere?

I have not used SQL-Anywhere for many years however the following statement should work

select c.column_name
from systabcol c 
   key join systab t on t.table_id=c.table_id 
   where t.table_name='tablename'

This was cribbed directly from an earlier question

How to list down all tables in a schema in sybase?

try like below by using sysobjects

select *
from sysobjects

and below will return your all tables of current database

select convert(varchar(30),o.name) AS table_name
from sysobjects o
where type = 'U'
order by table_name

Sybase docs