Check table exists

Question

I need to check if a table exists in a database. I currently develop using Yii2.

My case is a bit different from this question because the table to be checked is not (and can not be) a model.

I have tried (new \yii\db\Query())->select('*')->from($mysticTable)->exists());

The above throws a yii\db\Exception because, according to question linked above, the yii\db\Query() class tries to ->queryScalar() when asked if ->exists(). Invariably, this method checks if the result-set exists.

How do I check if a table exists?

score 26 · Accepted Answer · answered Jul 28 '15 at 08:53

26

For Yii2 you can use:

$tableSchema = Yii::$app->db->schema->getTableSchema('tableName');

If the table does not exist, it will return null, so you can check returned value for being null:

if ($tableSchema === null) {
    // Table does not exist
}

You can find this method in official docs here.

answered Jul 28 '15 at 08:53

arogachev

33,150
7
114
117

5

And for yii1: use `getTable` instead of `getTableSchema`. – Olle Härstedt Apr 26 '16 at 21:42

score 0 · Answer 2 · answered Jul 28 '15 at 08:55

Good that you get an exception. Simply parse the exception message. You would get a very very specific message and SQL error code for missing table.

That is what I do when checking e.g. IF an error was due to something that can be recovered, say a broken connection, versus some other error.

OR I see that many people have pointed out much more direct ways of getting that information.

score 0 · Answer 3 · edited May 23 '17 at 12:17

0

A spin off @msfoster's answer got me closer to a solution in yii2

/**
 * @param $tableName
 * @param $db string as config option of a database connection
 * @return bool table exists in schema
 */
private function tableExists($tableName, $db = null)
{
    if ($db)
        $dbConnect = \Yii::$app->get($db);
    else
        $dbConnect = \Yii::$app->get('db');

    if (!($dbConnect instanceof \yii\db\Connection))
        throw new \yii\base\InvalidParamException;

    return in_array($tableName, $dbConnect->schema->getTableNames());
}

This also serves multiple databases.

edited May 23 '17 at 12:17

Community

1
1

answered Jul 28 '15 at 08:55

Igbanam

5,904
5
44
68

if someone could put this into the yii2 source. that would be nice – Igbanam Jul 28 '15 at 08:56
Did you see my answer? You can do it with framework built-in methods, no need to write own methods to achieve that. – arogachev Jul 28 '15 at 09:09
@arogachev there are no such straightforward built-in methods in Yii 2. And, I have multiple databases so `Yii::$app->db` isn't sufficient. – Igbanam Jul 28 '15 at 15:21

Check table exists

3 Answers3