• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

mysql display all table values and everything in tables

SELECT * FROM INFORMATION_SCHEMA.TABLES
displays all the tables

i would like a command that displays all the tables and all the fields in the tables
0
rgb192
Asked:
rgb192
  • 5
  • 2
1 Solution
 
profyaCommented:
You need to run many queries to get the information you need. This link helps: http://www.razorsql.com/articles/mysql_admin_queries.html
0
 
profyaCommented:
You can customize this query I made:

SELECT T.TABLE_NAME, C . *
FROM information_schema.TABLES T
INNER JOIN information_schema.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_SCHEMA <> 'mysql'

Open in new window

0
 
profyaCommented:
TABLE_SCHEMA represents the database name, you can specify it for a specific database.
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
profyaCommented:
This idea also may help:

    Select OBJECT_TYPE,OBJECT_NAME
    FROM
    (
    Select 'TABLE' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
    Union
    Select 'VIEW' as OBJECT_TYPE, TABLE_NAME as OBJECT_NAME, TABLE_SCHEMA as OBJECT_SCHEMA from information_schema.VIEWS
    Union
    Select 'INDEX[Type:Name:Table]' as OBJECT_TYPE, concat(CONSTRAINT_TYPE,' : ',CONSTRAINT_NAME,' : ',TABLE_NAME) as OBJECT_NAME,TABLE_SCHEMA AS OBJECT_SCHEMA from information_schema.TABLE_CONSTRAINTS
    Union
    Select 'Procedure/Functions' as OBJECT_TYPE, ROUTINE_NAME as OBJECT_NAME, ROUTINE_SCHEMA as OBJECT_SCHEMA from information_schema.ROUTINES
    ) R
0
 
rgb192Author Commented:
SELECT T.TABLE_NAME, C . *
FROM information_schema.TABLES T
INNER JOIN information_schema.COLUMNS C ON T.TABLE_NAME = C.TABLE_NAME
WHERE T.TABLE_SCHEMA <> 'mysql'

this query finds the table info for all the databases.. thanks...

could you find the data in the columns for one database
for example  (firstname, lastname, email)
i want to see (bob, smith, bobsmith@aol.com)
                      (mary, smith, marysmtih@aol.com)
0
 
profyaCommented:
This is another question man ;)
0
 
rgb192Author Commented:
thanks
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now