Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    5

    Unanswered: How to know if a table or column exists

    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.

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: How to know if a table or column exists

    Originally posted by july9th
    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

  3. #3
    Join Date
    Mar 2004
    Posts
    5

    Re: How to know if a table or column exists

    Originally posted by ika
    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?

  4. #4
    Join Date
    Feb 2004
    Location
    Vienna/Austria
    Posts
    7
    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

  5. #5
    Join Date
    Mar 2004
    Posts
    5
    Originally posted by willi.st
    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.

  6. #6
    Join Date
    Feb 2004
    Location
    Vienna/Austria
    Posts
    7
    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

  7. #7
    Join Date
    Mar 2004
    Posts
    5

    Red face CALL TO ALL SMARTIES

    Originally posted by willi.st
    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?!?!?

  8. #8
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: CALL TO ALL SMARTIES

    Originally posted by july9th
    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.

  9. #9
    Join Date
    Mar 2004
    Posts
    8

    Re: CALL TO ALL SMARTIES

    Originally posted by july9th
    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?!?!?
    Use SELECT * FROM .... LIMIT 0 ... and check what column names exists ...

  10. #10
    Join Date
    Mar 2004
    Posts
    5

    Re: CALL TO ALL SMARTIES

    Originally posted by ajandris
    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?

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54

    Re: CALL TO ALL SMARTIES

    Originally posted by july9th
    "... 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

  12. #12
    Join Date
    Mar 2004
    Posts
    8

    Re: CALL TO ALL SMARTIES

    Originally posted by july9th
    "... 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?
    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().
    Last edited by ajandris; 03-31-04 at 08:26.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •