How to select all column name from a table in laravel?

Question

I have tried to get all column names from a table Teller

Function:

public function getTableColumns($tables)
{
    return DB::select(DB::raw('SELECT 
                                    COLUMN_NAME, 
                                    DATA_TYPE, 
                                    COLUMN_DEFAULT
                                FROM 
                                    INFORMATION_SCHEMA.COLUMNS
                                WHERE 
                                    table_name = `Teller`'));
}

Reba

Answer 1

You can get all columns name by simply doing that...

use Illuminate\Support\Facades\Schema;

use Illuminate\Support\Facades\DB;

public function getTableColumns($table)
{
    return DB::getSchemaBuilder()->getColumnListing($table);

    // OR

    return Schema::getColumnListing($table);

}

Kenya

The problem here when doing joins is that you need to prefix the column names with the table name like: $tableName =...; $columns = Schema::getColumnListing($tableName); $columns = array_map(function ($elem) use ($tableName) {return $tableName . '.' . $elem;}, $columns); – Iranian

Excellent! It's working – Demure

Answer 2

Get Table Name From Model

$product = new Product;
$table = $product->getTable();
print_r($table);

Get Table Column Name From Model

<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Product extends Model
{   
    public function getTableColumns() {
        return $this->getConnection()->getSchemaBuilder()->getColumnListing($this->getTable());
    }
}

Now you will get all columns of "products" table and if you need it in controller then you can get it by following way :

 $product = new Product;
 $columns = $product->getTableColumns();
 print_r($columns);

Hevesy

Answer 3

Just in-case if you have multiple databases connections, try following:

Add in the top of your php script

use Illuminate\Support\Facades\Schema;

Retrieve anywhere in your code

With Database Connection

$columns = Schema::Connection('business')->getColumnListing('users'); // 'business' is your database connection

        echo "<pre>";
        print_r($columns);
        exit();

Without Database Connection

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

    echo "<pre>";
    print_r($columns);
    exit();

Told

Answer 4

You only need extract the keys from the query response

array_keys(json_decode(json_encode($table[0]), true))

Abyss

Answer 5

As of Laravel 6.x this works:

                    $db = DB::connection()->getPdo();
                    $rs = $db->query('SELECT * FROM Teller LIMIT 0');
                    for ($i = 0; $i < $rs->columnCount(); $i++) {
                            $col = $rs->getColumnMeta($i);
                            $columns[] = $col['name'];
                    }
                    print_r($columns);

The hint here is just to go around eloquent (which should just provide a simple way to do this, but clearly does not) and grab the PDO object and then use the answer from the same question for straight PDO access

This will also work when there is no database selected by replacing 'Teller' with databasename.Teller

HTH,

-ft

Tolerance

Answer 6

Another way from a model using collections.

collect(User::first())->keys();

Kablesh

Answer 7

@Eduardo Wallace method has it simplest; just return the first value from the table, strip off the values leaving the keys

$T1 = table_name::first();
$table_columns = array_keys(json_decode($T1, true));

If you don't need all the columns, strip away unneeded columns, leaving only the ones needed:

$needed_columns = array_diff($table_columns, ['unneeded_1', 'unneeded_2']);

You can argue the method used, simplest to me anyways!

Alius

Answer 8

$db = [];
$tables =  DB::select('SHOW TABLES');

    foreach ($tables as $key => $table) {
        $name = $table->Tables_in_app;
        $app[$name] =  DB::getSchemaBuilder()->getColumnListing($name);
    }

return $db;

Jackstay

Answer 9

You can get an idea with this script

    $columns = array();
    foreach(\DB::select("SHOW COLUMNS FROM $table") as $column)
    {
       //print_r($column);
        $columns[$column->Field] = '';
    }
  
    return $columns;

Shuttering

Answer 10

in 12.17.22 it's
extremely simple

$columns=Schema::getColumnListing('my_table_name');
//e.g. your table's name is 'my_table_name' or 'Teller' or   'posts'
// and then
$fetchedSet= Post::select($columns)->where('sender_id','>',100)->get();

And you are done

Linnealinnean

Answer 11

$columns = DB::select("SHOW COLUMNS FROM table_name");

//This code will return column array as table defined in database.
return array_column($columns, 'Field');

//This code will return column array asc order of column name.
return Schema::getColumnListing('table_name');

Hotpress

You should add explanation. – Abecedary

You can get all columns name as you defined in database one after one by simply doing first block code. – Hotpress

You can get all columns array in asc order by doing 2nd return block code – Hotpress

Answer 12

this is work for me

use Illuminate\Support\Facades\DB;

getTableColumns((new Model())->getTable());    

function getTableColumns($table){
   return DB::getSchemaBuilder()->getColumnListing($table);
}

Internment

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Foggy

Answer 13

You could simply write:

public function getTableColumns($tables)
{
    return DB::select(
        DB::raw('SELECT * FROM `Teller`')
    );
}

If you have a Teller model you can also use Teller::all();

Update

To get all column name you can run SHOW FIELDS Teller

Lucent

Get Column name not get value in each column – Reba

I updated my answer. But there is no Laravel/Eloquent call for that. – Lucent

Answer 14

You can use this

DB::table('table_name')->get();

Hades

This is for getting data. The question was for getting column names, which should work even if no data is there. – Tolerance