How do Ilist tables based on column name in sybase?

Question

How do I list all the tables in a database that contain a column with a given name?

Can you be more specific? Do you want a list of columns in a table, or are you looking for all tables that contain a column, or something else? — Jonathan B, Jan 03 '11 at 14:56

score 24 · Answer 1 · answered Mar 16 '11 at 00:09

Try this:

select distinct object_name(id)
from syscolumns
where name = 'my_column_name'
order by object_name(id)

or this way:

select distinct so.name
from syscolumns sc
  , sysobjects so
where sc.id = so.id
and   sc.name = 'my_column_name'
order by so.name

is that what you've been looking for?

score 4 · Answer 2 · answered Jan 11 '17 at 16:11

4

You can also use sp_columns stored procedure.

sp_columns @column_name = '%column_name%'

More info here: SyBooks Online (Getting Help On Database Objects)

answered Jan 11 '17 at 16:11

Miguel

1,575
1
27
31

score 3 · Answer 3 · edited May 10 '17 at 15:01

3

The below query can be used to get column_name with table_name as well.

This will help the user identify the table to which the column_name belongs.

select distinct tbl_col.name as Field_name,tbl_object.name as Table_name
from syscolumns tbl_col
  , sysobjects tbl_object
where tbl_col.id = tbl_object.id
order by tbl_object.name

edited May 10 '17 at 15:01

SWalters

3,615
5
30
37

answered May 10 '17 at 14:44

Amol

31
2

Priceless. Thank you. – datv Feb 22 '18 at 07:40
priceless indeed – Abi Sep 27 '19 at 01:54

score 0 · Answer 4 · answered Nov 14 '19 at 15:40

0

Try this:

sp_columns @column_name = '%column_name%'

answered Nov 14 '19 at 15:40

Raunak Jhawar

1,541
1
12
21

How do Ilist tables based on column name in sybase?

4 Answers4