Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

1 Answer

up vote 3 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` (
  `UserId` INT(10) UNSIGNED NOT NULL,
  `RoleId` SMALLINT(5) UNSIGNED NOT NULL,
  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`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

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
add comment

Your Answer

 
discard

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.