Search code examples
sap-ase

all the varchar columns in all the tables from sybase

Does anybody know how can we get the list of all the columns in all the tables which are varchar? i need it in below format:

<column_name> <table_name>

i tried this:

select o.name [TableName], c.name [ColumnName]  from sysobjects o 
inner join syscolumns c on c.id = o.id inner join systypes t 
on t.usertype = c.usertype where o.type = 'U' and o.name in ("MYTABLE")

but the above gives the list of all columns. if i can find a way then i can put all the table names inside last braces.

Solution

  • You can use where systypes.name = 'varchar', or systypes.type = 39, or syscolumn.type = 39 to narrow your query to just varchar columns.

    select o.name, c.name from sysobjects o, syscolumns c
where o.id = c.id
and c.type = 39
and o.type = "U"