[SQL] Information schema

on

MySQL:

How to rename a database

1.
For InnoDB, create the new empty database, then rename each table in turn into the new database:

[code language=”sql”]RENAME TABLE old_db.table TO new_db.table;[/code]

This also work fine in MyISAM
2.
Create the destination database before running the script generated from the command.

[code language=”sql”]SELECT concat(‘RENAME TABLE $source.’,table_name, ‘ TO $destination.’,table_name, ‘;’) FROM information_schema.TABLES WHERE table_schema=’$source’;[/code]

3.
For smaller database you can use phpmyadmin or simply dump databse once and export again into new database.

MySQL table schema – how to show the schema of a MySQL table

To show the schema for a MySQL database table, use the MySQL “desc”/”describe” command

[code language=”sql”]
mysql> use $database_name;
mysql> desc $table_name;[/code]

MySQL Information schema

In relational databases, the information schema is an ANSI standard set of read-only views which provide information about all of the tables, views, columns, and procedures in a database.

How to show size per database:

[code language=”sql”]
SELECT table_schema ‘database’, concat( round( sum( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , ‘M’ ) size FROM information_schema.TABLES WHERE ENGINE=(‘MyISAM’ || ‘InnoDB’ ) GROUP BY table_schema
MySQL Performance Schema
[/code]

References:

http://mysqlresources.com/documentation/db-table-schema
http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html
http://dev.mysql.com/doc/refman/5.1/en/tables-table.html
http://stackoverflow.com/questions/67093/how-do-i-quickly-rename-a-mysql-database-change-schema-name

Leave a Reply

Your email address will not be published. Required fields are marked *