"Create table if not exists" - how to check the schema, too?

sql database h2 create-table
Joonas Pulakka picture Joonas Pulakka · Mar 19, 2010 · Viewed 16.2k times · Source

Is there a (more or less) standard way to check not only whether a table named mytable exists, but also whether its schema is similar to what it should be? I'm experimenting with H2 database, and

CREATE TABLE IF NOT EXISTS mytable (....)

statements apparently only check for the table´s name. I would expect to get an exception if there's a table with the given name, but different schema.

Answer

isapir picture isapir · Sep 4, 2012 
SELECT  *
FROM    INFORMATION_SCHEMA.TABLES
WHERE   TABLE_NAME      = 'TableName'
    AND TABLE_SCHEMA    = 'public'

Related questions

Quick SQL question: Correct syntax for creating a table with a primary key in H2?

I'm currently starting a new Java application using the H2 database, but I have some confusion about basic SQL use for creating tables. How do I make a table of entries (strings) each with unique, auto-incrementing, non-null, integer primary keys? …

Read More
Can I have H2 autocreate a schema in an in-memory database?

(I've already seen the H2 database In memory - Init schema via Spring/Hibernate question; it is not applicable here.) I'd like to know if there's a setting in H2 that will allow me to auto-create a schema upon connecting …

Read More
Defining a Foreign key constraint in H2 Databases

I am new in coding so I made a tables in SQL server and it worked, so i used the same command in H2 and it said I have a syntax problems with the second table, someone can help? CREATE …

Read More