Create table within if exists block in Sybase ASE?

So, how is it that you try to check the existance of a sybase table. Well the answer is pretty simple. Fire the following query and you are right into what you want -

if exists(select 1 from sysobjects where name = <<Table Name>> and type = 'U')
begin
  <<SQL statements>>
end
go

Okay, so we know how to check the existance of a table, but what if we say that we want to check if a table exists in system catalogue or not and if not then create the table. What to do in this case? Well the obvious syntax that comes o our mind as first thought is as follows -

if not exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
  create table MyTab
  (
     Id int,
     Name varchar(100),
     Age int
  )
end
else
begin
  print 'Table already exists'
end
go

Well, is it correct? I dont think so. Why? Because if in case MyTab table was already existing in the sysobjects table and we run this query then we will get the error that table MyTab already exists in the system catalogue. So what is wrong with the statement?

To understand this we need to look deeper into how Adaptive Server Enterprise tries to execute the above query. Well in above case when we fire the if exists clause and define the create table syntax after it, the server first tries to check the presence of the MyTab table in the system catalogue and once it is found the server further finds that the next statement tries to create a table with a name that is already present in the system catalogue and hence throws the error.

In case if we would have defined a drop table command within the if exists block then the command would have run successfully as in that case the server checks if the required table is already in the system catalogue and if so tries to drop it which is prefectly valid. The drop command is as follows -

if exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
  drop table MyTab
end
else
begin
  print 'MyTab does not exists.'
end
go

So, coming back to the question - What to do in case we require to create table within the if exists block in Sybase ASE? Is there any way by which we can achieve this?

Well the answer is - Yes, ofcourse. How? Lets figure it out...


As we can see in the above example we encountered the error only while creating the table because the server first checked the existance of the table in sysobjects table before creating the same and evaluating the condition, so we need to trick our server to deffer this check to runtime and so we will use the execute immediate or exec command to execute the create table statement as a dynamic sql. So, now with dynamic sql to our rescue we can place the create table syntax in a string and execute the statement within the if exists block which will trick the server to evaluate the exec statement only at the runtime and not at the compile time and hence avoiding the error. The syntax with dynamic sql is as follows -

if not exists(select 1 from sysobjects where name = 'MyTab' and type = 'U')
begin
  exec('create table MyTab (Id int, Name varchar(100), Age int)')
end
else
begin
  print 'MyTab table already exists in the system catalogue'
end
go

Simple... Isn't it?