Search results
May 22, 2009 · To get the whole database structure as a set of CREATE TABLE statements, use mysqldump: mysqldump database_name --compact --no-data. For single tables, add the table name after db name in mysqldump. You get the same results with SQL and SHOW CREATE TABLE: SHOW CREATE TABLE table; Or DESCRIBE if you prefer a column listing:
Sep 30, 2009 · SHOW CREATE TABLE. The SHOW CREATE TABLE statement can be used to retrieve the CREATE TABLE statement to reproduce this table. For example: SHOW CREATE TABLE yourTable; Or with a prefixed database name: SHOW CREATE TABLE yourDatabase.yourTable; This will produce a single row with to columns, the table name and the CREATE STATEMENT.
Mar 8, 2012 · Additional You can get size of the mysql databases as following. SELECT table_schema "DB Name", Round(Sum(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB". FROM information_schema.tables. GROUP BY table_schema. ORDER BY `DB Size in MB` DESC; Result. DB Name | DB Size in MB.
Nov 3, 2014 · 555. To get the name of all tables use: SELECT table_name FROM information_schema.tables; To get the name of the tables from a specific database use: SELECT table_name FROM information_schema.tables. WHERE table_schema = 'your_database_name'; Now, to answer the original question, use this query: INSERT INTO table_name.
Oct 14, 2008 · As @Barmar says, this is entirely wrong; it will show the foreign keys belonging to the specified table, but will not show foreign keys pointing TO the table, which is what the question asks for. No idea how this got 50 upvotes; I guess people ended up here when really they were looking for the answer to the opposite question, found their ...
May 23, 2023 · Please note. The collation shown in the show table status is not the character set of the table. The collation tells you how the characters are sorted / compared. e.g. utf8_bin_ci compares data without regarding the case (case insensitive, so "m" and "M" are the same), utf8_bin_cs compares with case sensitivity (so "m" and "M" are distinct).
Jun 14, 2012 · Locks you take out after the mdl instrument has been enabled can be seen by running a SELECT against the performance_schema.metadata_locks table. As noted in the docs, GET_LOCK locks have an OBJECT_TYPE of 'USER LEVEL LOCK', so we can filter our query down to them with a WHERE clause: mysql> SELECT GET_LOCK('foobarbaz', -1);
May 31, 2017 · You can query INFORMATION_SCHEMA.TABLES and get the collation for a specific table: SELECT. TABLE_SCHEMA. , TABLE_NAME. , TABLE_COLLATION. FROM INFORMATION_SCHEMA.TABLES. WHERE TABLE_NAME = 't_name'; that gives a much more readable output in contrast to SHOW TABLE STATUS that contains a lot of irrelevant information.
Mar 6, 2011 · To see the index for a specific table use SHOW INDEX: SHOW INDEX FROM yourtable; To see indexes for all tables within a specific schema you can use the STATISTICS table from INFORMATION_SCHEMA: SELECT DISTINCT. TABLE_NAME, INDEX_NAME. FROM INFORMATION_SCHEMA.STATISTICS. WHERE TABLE_SCHEMA = 'your_schema'; Removing the where clause will show you ...
Dec 2, 2009 · In addition, removing the last where clause will show all columns for a database which are protected by unique constraints: SELECT. CONSTRAINT_NAME, TABLE_NAME, COLUMN_NAME. FROM information_schema.KEY_COLUMN_USAGE. WHERE. CONSTRAINT_NAME LIKE 'UNIQ%'. AND TABLE_SCHEMA = 'your_database_name'.
