Database Developers' Quick-Reference to MySQL DDL

Published on
9,262 Points
Last Modified:
Community Pick
Some time back I wrote an article to help DB developers get started in MySQL.
Database Developers' Quick-Reference to MySQL

Now I'm adding some Data Definition Language (DDL) statements to that.

1. Describe

What does the silly table look like?  What are it's fields and what are their types?
exec sp_help tablename

Open in new window

Describe tablename;

Open in new window

2. information_schema tables

information_schema is presented as its own DB.  Actually, I think it is its own Schema, but all the stuff a SQL Server developer would group as a database, MySQL groups as a Schema.

Most of your schema queries are going to one or more of the information_schema tables.
Consider information_schema.tables roughly equivalent to sys.tables and information_schema.columns roughly equivalent to syscolumns.

So ... the next several items will make use of that knowledge.

3. Does Table X Exist in my MySQL DB?

This is as easy as in SQL Server, but a bit different. In MySQL it's:
SELECT COUNT( * ) FROM information_schema.tables WHERE table_schema = 'MyDBName' AND table_name = 'MyTableName'; 

Open in new window

4. Does Table X Have Column Y in my MySQL DB?

This one's actually a bit easier than in MS SQL Server as no join is required ...
SELECT count( * ) FROM information_schema.columns WHERE table_schema = 'MyDBName' AND Table_Name = 'MyTableName' AND column_Name = 'MyColumnName'; 

Open in new window

5. Add table only if it does not exist

This one's better in MySQL than in MS SQL Server also:
CREATE TABLE IF NOT EXISTS tablename ( ... ); 

Open in new window

6. Drop Table only if it Exists

This one's better in MySQL than in MS SQL Server also
Again, there's just an If Exists at the end of the DDL statement.

Open in new window

7. Add a column only if it does not exist

This one's clunky! YUCK!!!
Because the IF statement is only available within procedures and functions, you have to have a procedure that does this. The solution is here:

8. Drop a column only if it exists

Clunky just like the above:
delimiter '//' ;
create procedure DropColumnIfExists(
	IN dbName tinytext,
	IN tableName tinytext,
	IN fieldName tinytext)
		SELECT * FROM information_schema.COLUMNS
		WHERE column_name=fieldName
		and table_name=tableName
		and table_schema=dbName
		set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
			' DROP ',fieldName);
		prepare stmt from @ddl;
		execute stmt;
delimiter ';' ;

Open in new window

I hope these help ... and if you'll send  me some more tips, I'll add them.  I'll probably add some more anyway as I keep working in MySQL.
Enjoy this complimentary article view.

Get unlimited access to our entire library of technical procedures, guides, and tutorials written by certified industry professionals.

Get 7 days free
Click here to view the full article

Using this article for work? Experts Exchange can benefit your whole team.

Learn More
Experts Exchange is a tech solutions provider where users receive personalized tech help from vetted certified professionals. These industry professionals also write and publish relevant articles on our site.
Ask questions about what you read
If you have a question about something within an article, you can receive help directly from the article author. Experts Exchange article authors are available to answer questions and further the discussion.
Learn from the best.