if table exists (DBI)

[Bishma]

Monks,

I'm working with a MySql database and I'm having a little trouble with one thing.

Is there a way to check if a table exists within a database without reading all of the tables into an array?

Basically I want to be able to do an if-else statement.
If table exists: add to it.
Else: Create it and add to it.

Thanks in advance.

[blokhead]

MySQL allows for this logic built into its statements, for example:

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

AFAIK, this is only valid for MySQL, and not other SQL variants. A quick way to get all of your tables is to send a SHOW TABLES query. It will return a list of all of the tables. Oops, looks like you knew that and wanted to avoid it.

blokhead

[erix]

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) |

( 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
#

)

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

[UnderMine]

Yes it is possible to do the requested checks and creation but this means the users has to have create privileges.

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

[Chief of Chaos]

Hi,
I only know these ways :


           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.


From : http://www.cslab.uky.edu/~jgao0/mproj/help.txt

I hope this may help you

[dragonchild]

The correct answer for MySQL (at least as of 5.0).

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;
}

[pg]

Check your MySQL document, as I know ORACLE has those tables used by admin, one of them holds a list of all tables, domains. If MySQL provide this table, then you can just query it. Perl is not really responsible for this.

[tommyw]

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.

[HelenCr]

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;

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

Helen