I want to fetch list of tables from mysql database from nodejs

Question

connection.query('SHOW tables', function(err, tables){ 
    console.log(tables);
});

this query returns :

[
  RowDataPacket { Tables_in_books: 'au' },
  RowDataPacket { Tables_in_books: 'authors' },
  RowDataPacket { Tables_in_books: 'customers' },
  RowDataPacket { Tables_in_books: 'persons' },
  RowDataPacket { Tables_in_books: 'user' }
]

But i only want the names. i.e. output should be au, authors, cutomers, persons, user. basically i just need name of tables in string format.

Siva Koteswara Rao · Accepted Answer · 2020-02-28 06:42:09Z

1

select table_name from information_schema.tables

where table_schema - 'books'

answered Feb 28, 2020 at 6:42

Siva Koteswara Rao

1311 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

parth agrawal Over a year ago

giving same result as I mentioned earlier

Siva Koteswara Rao Over a year ago

I think you have to add table_name in function(err, table_name)

parth agrawal Over a year ago

ya and also in console.log. console.log(table_name[0].TABLE_NAME);

Muhand Jumah · Accepted Answer · 2020-02-28 06:41:06Z

1

MySQL has a built in database that includes meta data about your databases, this database name is information_schema.

So you can query it by doing something as follows

SELECT table_name FROM information_schema.tables WHERE table_schema ='Database Name';

so for you, you can do this

connection.query(`SELECT table_name FROM information_schema.tables WHERE table_schema = abc`, function(err, tables){ 
    console.log(tables);
});

You can learn more about information_schema here https://dev.mysql.com/doc/mysql-infoschema-excerpt/5.7/en/

answered Feb 28, 2020 at 6:41

Muhand Jumah

1,9681 gold badge15 silver badges30 bronze badges

3 Comments

parth agrawal Over a year ago

returns undefined

Muhand Jumah Over a year ago

give more context please. What is the exact query you used and what is your db setup? what user permissions do you have?

Siva Koteswara Rao Over a year ago

where table_schema - 'abc' , we have to specify database name within quotes.

1baga · Accepted Answer · 2022-09-21 23:12:49Z

0

Your problem is a javascript problem. All you needed to do was loop through the array being returned and add the value of each object to an array.

const result = tables.map(table => table['Table_in_books'])

I think this should solve your problem

answered Sep 21, 2022 at 23:12

1baga

3384 silver badges16 bronze badges

Collectives™ on Stack Overflow

I want to fetch list of tables from mysql database from nodejs

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related