Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2007
    Posts
    32

    Unanswered: how to exit if the table already exists

    Hi All
    In my script to a create table, how can check if the table exists? If the table exists, don't show any error message and don't try to create table

    How come the following doesn't work?

    Code:
    if (not exists (select 1 from sysobjects where name="test" and type="U"))
    begin
            print "eeeeeeeeeeeeee"
    create table test
    (
            test char(1) not null,
    )
    end
    else
            print "aaaaaaaaaa"
    go
    I get the following error message
    "There is already an object named 'test' in the database"



    Please help

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Not a sybase guy, but does your conditional work? I mean, without the CREATE TABLE statement, does it print "aaa" instead of "eee"?

  3. #3
    Join Date
    Dec 2007
    Posts
    32
    After I create the "test" table, I tried applying the code.
    The screen prints aaaaaaaaa. However, before it prints aaaaaaaa, it also shows the error message that "test" table already exists error message (but eeeeeee doesn't get printed)

    Is it possible to modify the code, so that it doesn't print the table already exists error message?

  4. #4
    Join Date
    Aug 2002
    Location
    Madrid, Spain
    Posts
    97
    Embed the create table statement in a exec() function:

    exec ('create table...')

    Regards,
    Mariano

  5. #5
    Join Date
    May 2008
    Posts
    10

    RE: how to exit if the table already exists

    Hi tiger66,

    This is not a runtime error, this is a compilation error.

    Try this ..

    1. Make sure 'test' table does not exists on database initially.

    2. Create a proc for this

    CREATE PROC sp_tigers
    as
    if (not exists (select 1 from sysobjects where name="test" and type="U"))
    begin
    print "eeeeeeeeeeeeee"
    create table test
    (
    test char(1) not null,
    )
    end
    else
    print "aaaaaaaaaa"
    go

    3. Once compiled you should be able run it any times, without that error ..

    cheers...
    -samir
    TechFruits.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •