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

Question

I've searched for a bit but wasn't able to find a specific question on this. How do I obtain all the column names in a table whose names contain a specific string? Specifically, if the column name satisfies like %bal% then I would like to write a query that would return that column name and others that meet that criteria in Sybase.

Edit: The Sybase RDBMS is Sybase IQ.

Answer 1

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