我有一个具有多个模式的服务器。我需要找到特定的电子邮件地址。每个模式看起来几乎相同，它们具有相同的结构。我想做的是编写一个脚本来显示与给定电子邮件相关的dbname和specyfic数据。这是我的剧本，但行不通

DELIMITER $$

# Drop procedure if exists SearchAllDb $$

Create procedure SearchAllDb()

BEGIN
DECLARE DB_NAME Varchar(50);
DECLARE done INT default FALSE;
DECLARE CURSOR_ALL_DB_NAMES CURSOR FOR
SELECT schema_name from information_schema.schemata
WHERE schema_name like 'itools_%';

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN CURSOR_ALL_DB_NAMES;

myloop: LOOP
FETCH CURSOR_ALL_DB_NAMES INTO DB_NAME; 

IF done THEN LEAVE myloop;
END IF; 

WHILE done != TRUE DO

SET @SQL = CONCAT('select DB_NAME, id, sn, name, email 
from',DB_NAME,'.`open_cases` where email like 'xxx@yahoo.com'');
prepare stmt from @SQL;
execute stmt;
deallocate prepare stmt;

END WHILE; 

CLOSE CURSOR_ALL_DB_NAMES; 
END;

DELIMITER ;

我有一些类似的东西是用t-sql写的，它很有效。。。

DECLARE @DB_NAME VARCHAR(50);
DECLARE CURSOR_ALL_DB_NAMES CURSOR FOR
SELECT name FROM master.sys.databases
WHERE name like 'pickup-%' and name!= 'pickup-stored';

OPEN CURSOR_ALL_DB_NAMES;
FETCH NEXT FROM CURSOR_ALL_DB_NAMES INTO @DB_NAME 

WHILE @@Fetch_Status = 0
BEGIN
Print @DB_NAME;
exec ('select '''+ @DB_NAME + ''' as db, id, sn, email from ['+ 
@DB_NAME+'].dbo.requests where email in (''xxx@live.com'')');

FETCH NEXT FROM CURSOR_ALL_DB_NAMES INTO @DB_NAME 
END

CLOSE CURSOR_ALL_DB_NAMES;
DEALLOCATE CURSOR_ALL_DB_NAMES;