Eloquent: How to get an array of all table names

Introduction

When working with Laravel and Eloquent, developers may often need to programmatically retrieve a list of all table names within a given database. This can be particularly useful in situations such as dynamically generating models, performing database inspections, or when creating database management tools. In this tutorial, we’ll explore various methods to get an array of all table names using Eloquent ORM in Laravel.

Understanding Eloquent and its underlying DB facade is crucial for using these techniques effectively. Eloquent ORM provides an elegant and easy-to-use syntax for interacting with your database tables. However, for our purpose, we will slightly step outside the bounds of traditional ORM usage and leverage the DB facade which affords direct access to database operations.

Prerequisites

Before we dive into the code examples, make sure your Laravel environment is set up and running. Also, ensure you have the correct database configurations in your .env file, as we’ll be connecting to the database using those credentials.

Using the DB Facade

The DB facade in Laravel provides methods to interact directly with the database, bypassing the Eloquent model layer. One of these methods is the select method which can execute a raw query and return the result.

$tableNames = \DB::select('SHOW TABLES');

The raw query 'SHOW TABLES' is specific to MySQL databases. If you are using a different database system, make sure to adjust the query according to your DBMS (Database Management System).

Extracting the Table Names

After executing the raw query, we will receive an array of stdClass objects. Let’s transform it into a simple array of table names.

$tableNames = \DB::select('SHOW TABLES');
$tableNames = array_map(function (
 stdlass $tablePropertyString) {
 return array_values((array)$tableProperty)String)[0];
}, $tableNames);

The array_map function iterates through each stdClass object, casting it to an array and then extracting the table name.

Dynamic Method According to Connection

Laravel’s database configuration allows you to work with different database systems. We can write a more dynamic method that considers the underlying database:

public function getDatabaseTableNames()
{
 $connection = \DB::getDoctrineConnection();

 try {
 $schemaManager = $connection->getSchemaManager();

 $tables = $schemaManager->listTables();
 return array_map(function (
 Doctrine\DBAL\Schema\Table $table) {
 return $table->getName();
 }, $tables);
 } finally {
 $connection->close();
 }
}

This function utilizes the Doctrine DBAL (Database Abstraction Layer) that Laravel uses under the hood. It provides a more abstract and cross-DBMS compatible way of listing table names.

Note: Depending on your Laravel version, Doctrine’s DBAL may need to be explicitly installed via Composer.

Handling Non-Standard Table Structure

If your application uses a non-standard database schema, such as having prefixes for tables, you’ll need to consider this when retrieving your table names.

$prefix = \DB::getTablePrefix();
$tables = array_map(...);
$tables = array_map(function ($name) use ($prefix)a {
 return Str::startsWith($name, $prefix) ?
 substr($name, strlen($prefix)) : $name;
}, $tables);

This additional mapping removes the prefix from the table names if present.

Caching the Results

Fetching all table names can be an intensive operation, depending on the size and complexity of your database. In production, you should consider caching this data.

$tableNames = Cache::remember('table_names', 60, function () {
 return \DB::table($this->getDatabaseTableNames);
});

We use Laravel’s Cache facade to store the table names for 60 minutes to optimize performance.

Conclusion

In this tutorial, we have covered how to retrieve an array of all table names in a Laravel application using the power of Eloquent’s DB facade and Doctrine DBAL. While not a common task, it’s a powerful technique to have in your arsenal for certain types of applications or during development and debugging.

Understanding these methods allows you to directly manipulate and introspect your database in a Laravel-centric way, Be mindful of performance implications and security considerations when executing raw SQL queries and always validate and sanitize inputs if they’re coming from user input to prevent SQL injection attacks.

Happy coding!