Stored procedure

Question

I've been googling to find an answer but can't find anything. I have a cursor statement that pulls the name of the tables that are present in the database.

THe goal is: a stored procedure with 2 parameters, database1 and database2 comparing both databases and outputting the difference. database names are tab/space delimited

BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE db_tables VARCHAR(256);

DECLARE cursor1 CURSOR FOR 
        SELECT TABLE_NAME, TABLE_SCHEMA
        FROM information_schema.tables  
        WHERE TABLE_SCHEMA = db1
        AND TABLE_TYPE = 'BASE TABLE';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN cursor1;
FETCH cursor1 into db_tables;

WHILE done = FALSE DO
SET query1 = SELECT * FROM db1 WHERE table1 IN(table_name);
END WHILE;
CLOSE cursor1;

END

Answer 1

This uses the INFORMATION_SCHEMA.TABLES information

The Schema

create database db1;
create database db2;

create table db1.s1 (id int);
create table db1.s2 (id int);
create table db1.s3 (id int);

create table db2.s2 (id int);
create table db2.s3 (id int);
create table db2.s4 (id int);

The Query

select t1.table_name, 2 as 'not in this one'
from INFORMATION_SCHEMA.TABLES t1
where t1.table_schema='db1'
and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema='db2' and t2.table_name=t1.table_name)
union
select t1.table_name, 1 as 'not in this one'
from INFORMATION_SCHEMA.TABLES t1
where t1.table_schema='db2'
and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema='db1' and t2.table_name=t1.table_name)

The Results

+------------+-----------------+
| table_name | not in this one |
+------------+-----------------+
| s1         |               2 |
| s4         |               1 |
+------------+-----------------+

This means that table s1 is in database db1, but not in db2, and that table s4 is in the database db2, but not in db1.

Stored Proc

delimiter $$
create procedure showDBDiffInTableNames
( x1 varchar(40),x2 varchar(40) )
BEGIN
    -- 
    -- passed parameters, x1 is a string containing the name of a database
    -- x2 is a string containing the name of another database
    --
    select t1.table_name, 2 as 'not in this one'
    from INFORMATION_SCHEMA.TABLES t1
    where t1.table_schema=x1
    and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema=x2 and t2.table_name=t1.table_name)
    union
    select t1.table_name, 1 as 'not in this one'
    from INFORMATION_SCHEMA.TABLES t1
    where t1.table_schema=x2
    and not exists (select * from INFORMATION_SCHEMA.TABLES t2 where t2.table_schema=x1 and t2.table_name=t1.table_name);
END
$$
DELIMITER ;

Test it:

call showDBDiffInTableNames('x1','x2');

same results

t1 and t2 are just table aliases. See the manual page here. From the manual page:

The following list describes general factors to take into account when writing joins.

A table reference can be aliased using tbl_name AS alias_name or tbl_name alias_name:

....

I almost never write a query without an alias if, knowing ahead of time, I am going after two or more tables. It cuts down on the typing. They are especially common in self-joins (to the same table). You need a way to differentiate which one you are dealing with to remove Ambiguous errors from queries. So that is why that alias is in there. Plus, you will note that the table is gone after twice.

There are two ways you can write it, as seen in the pink/peach block above.