I am wanting to create rerunnable database structure scripts that will create a table, add a column, etc... I want to find out how to determine the presence of a database object before I try to create it.

In MS*SQL, I'd do something like this:

if NOT exists (SELECT sysobjects.name AS TableName, syscolumns.name AS ColumnName
FROM syscolumns INNER JOIN
sysobjects ON syscolumns.id = sysobjects.id
WHERE sysobjects.name = 'party'
AND syscolumns.name = 'email')
BEGIN
ALTER TABLE dbo.party ADD email varchar(255) NULL
END
GO

Is there some equivalent way to do this in MySQL?

Thanks immensely for any help here.

show databases - this shows all databases in MySQL
show tables - this shows all tables in specific database
describe <tablename> - this shows all columns (names) in specific table

thanks... this is a decent start... but how would I weave any one of these into a script I can run on various servers?

For example, how might Describe mytable output be used in some form of conditional structure to either decide to add or not add the column? the output of this function appears to be a query - how do I intercept the output and make use of it?

the MySQL Documentation says:

CREATE DATABASE [IF NOT EXISTS] dbname

http://www.mysql.com/documentation/m...REATE_DATABASE


CREATE TABLE [IF NOT EXISTS] tablename .... columns

http://www.mysql.com/documentation/m...l#CREATE_TABLE


hope this helps,

:-) Willi

Well, it does help but still doesn't quite hit the nail on the head, since I am wanting to know if my COLUMN already exists in an existing table...

ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...

alter_specification:
ADD [COLUMN] create_definition [FIRST | AFTER col_name ]

and unfortunately, ADD COLUMN doesn't seem to support the IF NOT EXISTS control... so... so far, I'm still stuck.

hm, sorry for my misunderstanding ....

as far as i can see, there is no way to resolve the problem with extensions (or functions) i MySQL itself. I would embed the solution in a script (shell-script/awk, php, etc.) or a programming language (java, c, etc.) to extract the relevant information from DESCRIBE tablename or SHOW COLUMS FROM table [FROM database] and to modify the database dynamically.

ok, sorry, that's all i know for now ....

Willi

grrrr.... I can't imagine there not being an easy way to know if a column exists.... come on, you smarties... someone come forward with the answer. PLEASE?!?!?

MySQL has not a data dictionary as Oracle or MSSQL.

Use SELECT * FROM .... LIMIT 0 ... and check what column names exists ...

"... and check what column names exist ...", HOW?

I need this to operate in a script... what conditional commands can I place around this SELECT that will let me inquire as to the presence of a certain column? Can you post an example of how to script your suggetion?

I don't think this can be done using just MySQL.

If you can use a scripting language (like Perl or PHP), then it is easy to see the MySQL table structure. Do a SHOW TABLE and examine the output to your heart's content!

-PatP

It depends on language U are using. For example, Java:

interface java.sql.DatabaseMetaData has method

public ResultSet getColumns(String catalog,
String schemaPattern,
String tableNamePattern,
String columnNamePattern)
throws SQLException

More info:
http://java.sun.com/j2se/1.4.2/docs/api/index.html
see java.sql

For PHP useful function is function mysql_list_fields().