identifying columns and tables

[Former Member]

In SQL Anywhere version 11.0

Can someone please give me syntax for ascertaining whether a table exists and also whether a column exists in the table.

Thanks,

Melvyn Polatchek

[chris_keating]

The system views SYSTABLE and SYSCOLUMN are deprecated and were replaced with SYSTAB and SYSTABCOL. Here is examples for checking for groupo.products table and the column 'name' in that table against the demo database. I added the table owner to the query since it is possible that there could be multiple tables of the same name but with different ownership. Replace "groupo', 'products', and 'name' with appropriate values for your table and column. An EXISTS might be used also to simple report whether a result is found for the query.

Table Query

  select
    string( su.user_name, '.', st.table_name) as qualified_table_name
  from 
    sysuser su join systab st on su.user_id = st.creator 
  where 
    user_name = 'groupo' and table_name = 'products'

Column Query

  select
    string( su.user_name, '.', st.table_name, '.', column_name ) as qualified_column_name
  from 
    sysuser su join systab st on su.user_id = st.creator 
        join systabcol stc on st.table_id = stc.table_id
  where 
    user_name = 'groupo' and table_name = 'products' and column_name = 'name'

[Former Member]

Thanks guys

Mel Polatchek

[MCMartin]

select * from systable key join syscolumn where table_name ='sysdbspace' and column_name='object_id'

Replace 'sysdbspace' with your table name and 'object_id' with the column you are looking for.