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

asked 06 Dec '11, 07:44

MelvynP's gravatar image

MelvynP
90669
accept rate: 0%

edited 07 Dec '11, 06:19

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819


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'
permanent link

answered 06 Dec '11, 09:58

Chris%20Keating's gravatar image

Chris Keating
7.8k49128
accept rate: 32%

edited 06 Dec '11, 09:59

Thanks guys

Mel Polatchek

(07 Dec '11, 05:28) MelvynP
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.

permanent link

answered 06 Dec '11, 09:40

Martin's gravatar image

Martin
9.0k130169257
accept rate: 14%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×143
×32

question asked: 06 Dec '11, 07:44

question was seen: 2,450 times

last updated: 07 Dec '11, 06:19