Sybase: Find in which table a column name is available

If you have a column name but do not know to which table it belongs to, you can use the syscolumns system table in the appropriate database.
syscolumns contains column metadata:

1> sp_help syscolumns
2> go
 Column_name Type        Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ----------- ----------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 id          int              4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 number      smallint         2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 colid       smallint         2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 status      tinyint          1 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 type        tinyint          1 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 length      int              4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 offset      smallint         2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 usertype    smallint         2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 cdefault    int              4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 domain      int              4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 name        longsysname    255 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 printfmt    varchar        255 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 prec        tinyint          1 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 scale       tinyint          1 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 remote_type int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 remote_name varchar        255 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 xstatus     int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 xtype       int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 xdbid       int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 accessrule  int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 status2     int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 status3     smallint         2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 computedcol int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 encrtype    int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 encrlen     int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 encrkeyid   int              4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 encrkeydb   sysname         30 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 encrdate    datetime         8 NULL  NULL     1 NULL         NULL      NULL             NULL                            0

The id column contains the object id of the containing table. The name column contains the name of the column.
In order to find the name of the containing table, you can use the sysobjects table in the appropriate database:

1> sp_help sysobjects
2> go
 Column_name  Type              Length Prec Scale Nulls Default_name Rule_name Access_Rule_name Computed_Column_object Identity
 ------------ ----------------- ------ ---- ----- ----- ------------ --------- ---------------- ---------------------- ----------
 name         longsysname          255 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 id           int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 uid          int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 type         char                   2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 userstat     smallint               2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 sysstat      smallint               2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 indexdel     smallint               2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 schemacnt    smallint               2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 sysstat2     int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 crdate       datetime               8 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 expdate      datetime               8 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 deltrig      int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 instrig      int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 updtrig      int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 seltrig      int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 ckfirst      int                    4 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 cache        smallint               2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 audflags     int                    4 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 objspare     unsigned smallint      2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0
 versionts    binary                12 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 loginame     varchar               30 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 identburnmax numeric               17   38     0     1 NULL         NULL      NULL             NULL                            0
 spacestate   smallint               2 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 erlchgts     binary                 8 NULL  NULL     1 NULL         NULL      NULL             NULL                            0
 sysstat3     unsigned smallint      2 NULL  NULL     0 NULL         NULL      NULL             NULL                            0

The id column is basically the same value as syscolumns.id. The name column is the name of the table. For user table, the type column contains ‘U’.

So basically you can execute the following to get the table name and column name for columns which name is ‘status’:

select o.name as TableName, c.name as ColumnName 
from sysobjects o, syscolumns c 
where o.id=c.id and o.type='U' and c.name='status'

Leave a Reply

Your email address will not be published. Required fields are marked *