# Finding out if a Mysql database contains a certain table

**URL:** <https://www.ruby-forum.com/t/finding-out-if-a-mysql-database-contains-a-certain-table/53991>\
**Category:** Ruby\
**Created:** [February 19, 2006, 5:31pm UTC](https://www.ruby-forum.com/t/finding-out-if-a-mysql-database-contains-a-certain-table/53991 "2006-02-19T17:31:35Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![ngw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/90db22/32.png) [@ngw](https://www.ruby-forum.com/u/ngw)\
**Post date:** [February 19, 2006, 5:31pm UTC](https://www.ruby-forum.com/t/finding-out-if-a-mysql-database-contains-a-certain-table/53991/1 "2006-02-19T17:31:35Z")

</div>

Hi \*,  
I need to find out if a MySQL db contains a certain table, if not I have  
to create it.  
I’m using the SHOW TABLES instruction that returns a Mysql::Result  
object, but after that I’m stuck, I don’t know how to process the  
resultset without writing awful code 😛  
I mean, I can resolve the problem by using a “state” variable inside a  
block and turn it to true if it finds the table, but I guess there’s a  
more appropriate way to handle this kind of problems.

result = dbh.query(“SELECT \* FROM album”)  
foo = false  
result.each {|row| foo = true if row.include?(“Killing Joke”)}  
if foo == true  
puts “HA!”  
end  
result.free

This is the code I have now, UGH !

TIA,  
ngw

---

<div class="post-metadata">

**Author:** ![ngw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/90db22/32.png) [@ngw](https://www.ruby-forum.com/u/ngw)\
**Post date:** [February 19, 2006, 6:32pm UTC](https://www.ruby-forum.com/t/finding-out-if-a-mysql-database-contains-a-certain-table/53991/2 "2006-02-19T18:32:58Z")

</div>

On Feb 19, 2006, at 11:28 AM, ngw wrote:

> Hi \*,  
> I need to find out if a MySQL db contains a certain table, if not I  
> have  
> to create it.  
> I’m using the SHOW TABLES instruction that returns a Mysql::Result  
> object, but after that I’m stuck, I don’t know how to process the  
> resultset without writing awful code 😛  
> I mean, I can resolve the problem by using a “state” variable inside a  
> block and turn it to true if it finds the table, but I guess there’s a  
> more appropriate way to handle this kind of problems.

A show tables command just returns a query of table names, 1 per row.

I’m not sure if you need to do something more complex, but in MySQL  
it’s valid to do:

CREATE TABLE IF NOT EXISTS

Caleb

---

<div class="post-metadata">

**Author:** ![ngw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/90db22/32.png) [@ngw](https://www.ruby-forum.com/u/ngw)\
**Post date:** [February 19, 2006, 7:48pm UTC](https://www.ruby-forum.com/t/finding-out-if-a-mysql-database-contains-a-certain-table/53991/3 "2006-02-19T19:48:52Z")

</div>

DÅ?a Nedeľa 19 Február 2006 18:31 Caleb T. napísal:

> A show tables command just returns a query of table names, 1 per row.

Sidenote: SHOW TABLES is a MySQLism, so watch out it you can’t rule out  
the  
data will be migrated to another DB.

David V.

---

<div class="post-metadata">

**Author:** ![ngw](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/n/90db22/32.png) [@ngw](https://www.ruby-forum.com/u/ngw)\
**Post date:** [February 20, 2006, 2:01am UTC](https://www.ruby-forum.com/t/finding-out-if-a-mysql-database-contains-a-certain-table/53991/4 "2006-02-20T02:01:46Z")

</div>

On 2/19/06, ngw [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> I mean, I can resolve the problem by using a “state” variable inside a  
> block and turn it to true if it finds the table, but I guess there’s a  
> more appropriate way to handle this kind of problems.

## Using Ruport as it is in the svn trunk (should be released today, i’d do something like this:

require “ruport”

Ruport::Config.source :default,  
:dsn =\> “dbi:mysql:my\_dbname”, :user =\> “myuser”, :password =\> “pass”

table = “some\_table\_i’m\_looking for”

show\_query = Ruport::Query.new(“show tables”)  
creation\_query = Ruport::Query.new("create table … ")

exit if show\_query.result.any? { |r| r[0].eql? table }  
creation\_query.execute

* * *

To demonstrate this, here it is in irb below.  
The same approach can be taken with any enumerable result set (Including  
DBI’s)

irb(main):001:0\> require “ruport”  
=\> true

irb(main):002:0\> Ruport::Config.source :default,  
irb(main):003:0\* :dsn =\> “dbi:mysql:bills\_development”, :user =\> “root”  
=\> nil

irb(main):004:0\> show\_tables = Ruport::Query.new “show tables”  
=\> #\<Ruport::Query:0xb778678c @cached\_data=nil, @raw\_data=nil,  
@password=nil, @statements=[“show tables”], @user=“root”,  
@dsn=“dbi:mysql:bills\_development”, @sql=“show tables”,  
@cache\_enabled=nil\>

irb(main):005:0\> puts show\_tables.result  
fields: ( Tables\_in\_bills\_development )  
row0: ( users )  
=\> nil

irb(main):006:0\> table = “apple”  
=\> “apple”

irb(main):007:0\> show\_tables.result.any? { |r| r[0].eql? table }  
=\> false

irb(main):008:0\> Ruport::Query.new(“create table #{table}”).execute  
=\> nil

irb(main):009:0\> show\_tables.result.any? { |r| r[0].eql? table }  
=\> false

irb(main):013:0\> Ruport::Query.new(“create table #{table} ( x int  
);”).execute  
=\> nil

irb(main):014:0\> show\_tables.result.any? { |r| r[0].eql? table }  
=\> true