Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2013

    Unanswered: Assign the variable in show tables.


    This is query for find the table in database.
    show tables from db_name where tables_in_client='table_name';

    i want to store this query into the one variable using procedure and then check using if/else.
    if table name is exists return 1 else 0;

    kindly help me how can do.
    Last edited by vasan; 07-25-13 at 04:32.

  2. #2
    Join Date
    Sep 2009
    San Sebastian, Spain
    Firstly if you are going to return a value then you should use a stored function and not a procedure.

    Second thing is that you should look in the database INFORMATION_SCHEMA which includes a table called TABLES. You can query this directly to see if the table exists in a particular database.

    To access a database and a table you would need to do something like

    SELECT count(*) INTO variable FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname' AND TABLE_NAME = 'table_name_you_are_searching';
    RETURN variable;

    If the table exists it will return 1 because it will have a count of 1 and if it does not exist then it will return 0.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    Follow me on Twitter

Posting Permissions

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