How do I look at column metadata in Sybase?

问题:

I have a list of columns a co-worker has given to me, but these columns reside in different tables in the DB. Is there some kind of tool in Sybase where I can query the table a column belongs to?

(I've tried Google-ing for this kind of tool, but no luck so far)

回答1:

syscolumns holds column metadata.

select * from syscolumns where name = ;

The id column in syscolumns is the id of the column's table, in sysobjects;

select b.name as tablename, a.name as columnname
from syscolumns a join systables b on (a.id = b.id) 
where b.type='U' and b.name = 'foo';

gets all columns for the table named 'foo'. The type = 'U' limits it to user tables.

select b.name as tablename, a.name as columnname
from syscolumns a join systables b on (a.id = b.id) 
where b.type='U' and a.name = 'foo';

gets all columns named 'foo'.

Most current version of ASE will use sysbojects instead of systables

回答2:

I had to make a few small change for it to work:

select  b.name as tablename, 
        a.name as columnname
from    dbo.syscolumns a 
join    sysobjects     b on a.id = b.id
where   b.type='U' 
and     upper(a.name) like '%FOO%'      -- wildcard search for column name
and     b.name = 'bar'                  -- exclude tables
order by b.name

回答3:

You can find the information for any column in:

SELECT * 
  FROM sys.syscolumns

If you want to know to what table a column belongs:

SELECT cname, tname 
  FROM sys.syscolumns
 WHERE tname IN ('col_1', 'col_2')

^{NOTE: I test this in Sybase ASA 9.}