CREATE TABLE IF NOT EXISTS my_table (
  foo INT,
  bar VARCHAR(25)
);
[download]

blokhead

this is only valid for MySQL,

PostgreSQL has IF [NOT] EXISTS for many objects; also for TABLE:

$ psql
psql (9.2.4)
Type "help" for help.
# CREATE TABLE IF NOT EXISTS my_table (foo INT, bar VARCHAR (25) );
CREATE TABLE
# \d my_table 
          Table "public.my_table"
 Column |         Type          | Modifiers 
--------+-----------------------+-----------
 foo    | integer               | 
 bar    | character varying(25) |
[download]

( So there is also: DROP <object> IF EXISTS ... ;

# drop table if exists my_table; -- exists
DROP TABLE
# drop table if exists my_table; -- no table is no problem:
DROP TABLE
#
[download]

)

Of course postgres can also ROLLBACK on DDL statements. Not many DBMS's implement *that*, I think.

My question is why is it that you are having to do this? Dropping and recreating tables has a lot more issues than just truncating the data in the table.

If you are just creating temporary tables for doing data processing that get removed later it is sometimes a good idea to create these on start up of the database.

Oracle, SQL Server and Sybase have methods of doing this but with mySQL it is usually better to add a simple connect and create script to the end of the daemon start up with a drop before the shutdown.

Hope this helps
UnderMine

           List names of all tables and views
            
After the connection  to Oracle or  Sybase or MySQL  has 
been established, the names of all tables and views that 
belong to the user are displayed.  User can then  select 
one by clicking it (single selection).  
 
The list of tables and  views is retrieved by using  the 
following queries:  
(1) Oracle:
    select table_name from user_tables;
    select view_name from user_views;
(2) Sybase:
    select sysobjects.name 
    from sysusers, sysobjects 
    where sysobjects.uid=sysusers.uid
          and (sysobjects.type='U' or sysobjects.type='V') 
          and sysusers.name='XXX';
(3) MySQL:
    show tables;
Note that MySQL does not support views.
[download]

sub table_exists {
    my ($dbh, $table, $schema) = @_;

    unless ($schema) {
        ($schema) = $dbh->selectrow_array('SELECT DATABASE()');
    }

    my ($exists) = $dbh->selectrow_array(
        "SELECT COUNT(*) FROM information_schema.tables WHERE table_sc
+hema = ? AND table_name = ?",
        undef, $schema, $table,
    );

    return $exists > 0;
}
[download]

Select the first row of the first column from the table.

• If a value is returned, the table exists, and is populated.
• If no value is returned, the table exists but is empty.
• If an error is raised, the table does not exist.

--
Tommy
Too stupid to live.
Too stubborn to die.

Although this thread is 11 years old, since this is a basic question, then for the benefit of PerlMonks users, here is the "right and efficient" answer (in my opinion):

for MS SQL Server:

(Assume the table name you are testing for existence is in variable $table_name):

my $query =  "IF EXISTS(SELECT * FROM sys.tables WHERE type = 'U' AND 
+name = '$table_name')
    SELECT 1 ELSE SELECT 0";
my $sth = $dbh->prepare( $query );
$sth->execute;
my $result = $sth->fetchrow_array;
[download]

$result should be 1 if $table_name exists in the database, and vice versa.

Helen