How to programmatically execute the «SHOW TABLE STATUS» SQL statement for a database table

dmitry_fedyuk · January 11, 2016, 8:07am

Use the \Magento\Framework\DB\Adapter\AdapterInterface::showTableStatus() method:

magento/magento2/blob/d50ee54/lib/internal/Magento/Framework/DB/Adapter/AdapterInterface.php#L179-L186


/**
 * Returns short table status array
 *
 * @param string $tableName
 * @param string $schemaName
 * @return array|false
 */
public function showTableStatus($tableName, $schemaName = null);

The default MySQL implementation is the \Magento\Framework\DB\Adapter\Pdo\Mysql::showTableStatus() method:

github.com

magento/magento2/blob/d50ee54/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L1042-L1058


/**
 * Show table status
 *
 * @param string $tableName
 * @param string $schemaName
 * @return mixed
 */
public function showTableStatus($tableName, $schemaName = null)
{
    $fromDbName = null;
    if ($schemaName !== null) {
        $fromDbName = ' FROM ' . $this->quoteIdentifier($schemaName);
    }
    $query = sprintf('SHOW TABLE STATUS%s LIKE %s', $fromDbName, $this->quote($tableName));


    return $this->rawFetchRow($query);
}

Usage examples

github.com

magento/magento2/blob/d50ee54/app/code/Magento/ImportExport/Model/ResourceModel/Helper.php#L47-L62


/**
 * Returns next autoincrement value for a table
 *
 * @param string $tableName Real table name in DB
 * @return int
 */
public function getNextAutoincrement($tableName)
{
    $connection = $this->getConnection();
    $entityStatus = $connection->showTableStatus($tableName);


    if (empty($entityStatus['Auto_increment'])) {
        throw new \Magento\Framework\Exception\LocalizedException(__('Cannot get autoincrement value'));
    }
    return $entityStatus['Auto_increment'];
}

github.com

magento/magento2/blob/d50ee54/lib/internal/Magento/Framework/DB/Adapter/Pdo/Mysql.php#L2456-L2466


/**
 * Check is a table exists
 *
 * @param string $tableName
 * @param string $schemaName
 * @return bool
 */
public function isTableExists($tableName, $schemaName = null)
{
    return $this->showTableStatus($tableName, $schemaName) !== false;
}

github.com

magento/magento2/blob/d50ee54/app/code/Magento/Backup/Model/ResourceModel/Db.php#L121-L138


public function getTableStatus($tableName)
{
    $row = $this->connection->showTableStatus($tableName);


    if ($row) {
        $statusObject = new \Magento\Framework\DataObject();
        foreach ($row as $field => $value) {
            $statusObject->setData(strtolower($field), $value);
        }


        $cntRow = $this->connection->fetchRow($this->connection->select()->from($tableName, 'COUNT(1) as rows'));
        $statusObject->setRows($cntRow['rows']);


        return $statusObject;
    }


    return false;
}

dmitry_fedyuk · January 26, 2016, 3:15pm