Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Is there any SQL query to get the syntax of an existing table in my MySql database?

Intent: Iam in the early stages of working with databases and I am using the MySql workbench for creating tables and updating them whenever needed. At some point there are cases where I would like to have the syntax that the table is now holding so as to document it or paste it in forums like StackOverflow(expecting that I can show all the constraints in fact the foreign key constraints on the table in an easy way).

Please let me know if there is any workaround for this.

share|improve this question
up vote 4 down vote accepted

You can use SHOW CREATE TABLE <table_name>; to get CREATE TABLE statement. Eg. SHOW CREATE TABLE acl_user_role; This will show you the table name and CREATE TABLE statement, something like:

CREATE TABLE `acl_user_role` (
  PRIMARY KEY (`UserId`,`RoleId`),
  KEY `FK_UserRole_Role` (`RoleId`),
  CONSTRAINT `FK_UserRole_Role` FOREIGN KEY (`RoleId`) REFERENCES `acl_role` (`RoleId`),
  CONSTRAINT `FK_UserRole_User` FOREIGN KEY (`UserId`) REFERENCES `user` (`UserId`)

If you want to know about Keys for a particular table, use something like SHOW KEYS FROM acl_user_role;

And if you want to get all keys and constraints referencing to other table (like foreign key references), use:

SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = '<your_table_name>' AND TABLE_SCHEMA = '<your_database_name>';

-- Example
SELECT * FROM `information_schema`.`KEY_COLUMN_USAGE`
WHERE TABLE_NAME = 'acl_user_role' AND TABLE_SCHEMA = 'db_shop';

Hope this may help you...

share|improve this answer
Bulls eye! Thankyou – Cdeez Nov 16 '12 at 10:53
@Cdeez - most welcome... :) – rajukoyilandy Nov 17 '12 at 8:51

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.