Database Developers' Quick-Reference to MySQL DDL: MySQL, DDL, Create Table

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?
MS SQL:

exec sp_help tablename

1:

Select all Open in new window

MySQL:

Describe tablename;

1:

Select all 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';

1:

Select all 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';

1:

Select all 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 ( ... );

1:

Select all 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.

DROP TABLE IF EXISTS tablename;

1:

Select all 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:
http://www.cryer.co.uk/brian/mysql/howto_add_column_unless_exists.htm

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)
begin
	IF EXISTS (
		SELECT * FROM information_schema.COLUMNS
		WHERE column_name=fieldName
		and table_name=tableName
		and table_schema=dbName
		)
	THEN
		set @ddl=CONCAT('ALTER TABLE ',dbName,'.',tableName,
			' DROP ',fieldName);
		prepare stmt from @ddl;
		execute stmt;
	END IF;
end;
//
delimiter ';' ;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

Select all 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.

Gain Access to all our Tech Resources

Get personalized answers

Ask unlimited questions

Access Proven Solutions

Search 3.2 million solutions

Read In-Depth How-To Guides

1000+ articles, demos, & tips

Watch Step by Step Tutorials

Learn direct from top tech pros

And Much More!

Your complete tech resource

30-day free trial. Register in 60 seconds.

Database Developers' Quick-Reference to MySQL DDL

Describe

information_schema tables

Does Table X Exist in my MySQL DB?

Does Table X Have Column Y in my MySQL DB?

Add table only if it does not exist

Drop Table only if it Exists

Add a column only if it does not exist

Drop a column only if it exists

MySQL

DDL

Create Table

MySQL Server

About The Author

More Articles From DanielWilson

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MySQL Server Experts