How do I get database column names in Laravel?

Question

How can I get column names of a table in an array or object in Laravel 4, using Schema, DB, or Eloquent?

It seems that I can't find a ready to use function, maybe you have some custom implementations.

Answer 1

New Answer

At the time I gave this answer Laravel hadn't a way to do this directly, but now you can just:

$columns = Schema::getColumnListing('users');

Old Answer

Using attributes won't work because if you do

$model = new ModelName;

You have no attributes set to that model and you'll get nothing.

Then there is still no real option for that, so I had to go down to the database level and this is my BaseModel:

<?php

class BaseModel extends \Eloquent {

    public function getAllColumnsNames()
    {
        switch (DB::connection()->getConfig('driver')) {
            case 'pgsql':
                $query = "SELECT column_name FROM information_schema.columns WHERE table_name = '".$this->table."'";
                $column_name = 'column_name';
                $reverse = true;
                break;

            case 'mysql':
                $query = 'SHOW COLUMNS FROM '.$this->table;
                $column_name = 'Field';
                $reverse = false;
                break;

            case 'sqlsrv':
                $parts = explode('.', $this->table);
                $num = (count($parts) - 1);
                $table = $parts[$num];
                $query = "SELECT column_name FROM ".DB::connection()->getConfig('database').".INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = N'".$table."'";
                $column_name = 'column_name';
                $reverse = false;
                break;

            default: 
                $error = 'Database driver not supported: '.DB::connection()->getConfig('driver');
                throw new Exception($error);
                break;
        }

        $columns = array();

        foreach(DB::select($query) as $column)
        {
            $columns[] = $column->$column_name;
        }

        if($reverse)
        {
            $columns = array_reverse($columns);
        }

        return $columns;
    }

}

Use it doing:

$model = User::find(1);

dd( $model->getAllColumnsNames() );

Answer 2

You may try Schema::getColumnListing('tablename'):

$columns = Schema::getColumnListing('users'); // users table
dd($columns); // dump the result and die

Result would be something like this depending on your table:

array (size=12)
  0 => string 'id' (length=2)
  1 => string 'role_id' (length=7)
  2 => string 'first_name' (length=10)
  3 => string 'last_name' (length=9)
  4 => string 'email' (length=5)
  5 => string 'username' (length=8)
  6 => string 'password' (length=8)
  7 => string 'remember_token' (length=14)
  8 => string 'bio' (length=3)
  9 => string 'created_at' (length=10)
  10 => string 'updated_at' (length=10)
  11 => string 'deleted_at' (length=10)

Answer 3

You can dig down into DB's Doctrine instance.

$columns = DB::connection()
  ->getDoctrineSchemaManager()
  ->listTableColumns('table');

foreach($columns as $column) {
  print $column->getName();
  print $column->getType()->getName();
  print $column->getDefault();
  print $column->getLength();
}

edit: Doctrine is no longer (as of L4.1) installed by default (it's a 'suggested' rather than 'required' package), but can be added to your composer.json as doctrine/dbal to retain this functionality.

Answer 4

I think there's a couple different options, if you are using an Eloquent model, you can look at the getAccessibleAttributes() method, which in theory would give you all the columns of a model consider Eloquent seems them as properties.

For example, you'd be able to do something like this for your users table on a User Eloquent model.

$user = // Retrieve your User model.
$columns = User->getAccessibleAttributes();

Another Eloquent method to look at that's similar, but doesn't have the 'accessibility' requirement is the attributesToArray() method. The returned array of which should have your columns as a key. Then you can use the PHP function array_keys() to build an array of the keys, which would be your columns.

$user = // Retrieve your User model.
$columns = array_keys(User::attributesToArray());

Answer 5

I know it might not be the answer for everyone, but maybe you can grab one record, and get all keys of the data. Ex.

array_keys(User::first()->toArray());

Answer 6

If you have a Model instance you can retrieve like following:

    $table_name = $model->getTable();
    $connection = $model->getConnection();
    $schemaBulder = $connection->getSchemaBuilder();

    $columns_array = $schemaBulder->getColumnListing($table_name);

works for Laravel 5

Answer 7

You also can try this:

abstract class BaseModel extends Eloquent {

public function getColumnsNames()
{
    $connection = DB::connection();
    $connection->getSchemaBuilder();

    $table   = $connection->getTablePrefix() . $this->table;
    $grammar = $connection->getSchemaGrammar();
    $results = $connection->select($grammar->compileColumnExists(), array($connection->getDatabaseName(), $table));

    return $connection->getPostProcessor()->processColumnListing($results);
}
}

Answer 8

I use SQL Server and the Schema way worked for me:

$columns = array_keys(Schema::getConnection()
->getDoctrineSchemaManager()
->listTableColumns($yourModel->getTable()) );