6

I need to write a raw query in Laravel Database: Query Builder, That outputs size of specific table

In core mysql query is as following

SELECT table_name "Name_of_the_table", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2)
"Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "Name_of_the_Database" AND table_name ="Name_of_the_table";
Hemant Maurya
  • 119
  • 1
  • 4
  • 13

2 Answers2

11

You can get records by using raw query in laravel like:

$sql = 'SELECT table_name "Name_of_the_table", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2)
"Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "Name_of_the_Database" AND table_name ="Name_of_the_table"';

$results = DB::select($sql);
Qirel
  • 25,449
  • 7
  • 45
  • 62
6

You can use the query builder, since you can minimize the raw part to the table size:

$data = DB::table('information_schema.TABLES')
    ->where('table_schema', 'Name_of_the_Database')
    ->where('table_name', 'Name_of_the_table')
    ->select(
        'table_name as "Name_of_the_table"',
        'table_rows as "Rows Count"',
         DB::raw('round(((data_length + index_length)/1024/1024),2) as "Table Size (MB)"')
    )
    ->first();
Paul Spiegel
  • 30,925
  • 5
  • 44
  • 53
  • This helped a lot, thank you! I've used this to get the number of rows for a very large table, when count errors. I'm just rephrasing the question so that it shows up when you search for that too ;-) – tabacitu Sep 21 '20 at 12:44