We have multiple identical schemas. How can I store a query and easily apply it to any of multiple schemas? Bonus points for a drag & drop or point & click method of executing the query.

My first attempt was to set a variable for the schema, like this

SET @db = MySchema;

SELECT * from @db.mytable;

but I get Error Code: 1146. Table 'db.tagdescriptions' doesn't exist.

I'm using MySQL Workbench 6.3, connecting to MySQL Community Server (GPL) 5.6.19-log on Win64.

  • What version of MySQL and MySQL Workbench are you using? Those details will be quite helpful to people wanting to answer your question. =) – Kassandry Jan 14 '16 at 21:24
  • @Kassandry, I added the info to the question, thanks. – cp.engr Jan 14 '16 at 21:28

In addition to what Rick wrote:

If you just wanna have an ad hoc solution (not something you frequently use) you could also just have your query in the editor without a schema and sequentially make each of the schemas you want to process the default schema by double clicking on it's name in the schema tree. I.e. make first one default, run the query. Make the next one default and run the query, and so on.

| improve this answer | |
  • This suits my purposes well enough. I just waited a bit before accepting to see if anyone proposed something about as easy but less ad hoc. – cp.engr Feb 3 '16 at 1:12

Plan A:

Write a "Stored Procedure". Pass in the database name. Inside the SP, use CONCAT to build the SELECT, PREPARE, EXECUTE to run the SELECT, and DEALLOCATE to clean up.

Plan B:

USE MySchema;
SELECT * from mytable;
| improve this answer | |
  • (A) stored procedure - isn't that necessarily server-side? I want client side. (B) USE statement - I tried this in a query window, and it returns 0 rows; whereas SELECT * from MySchema.mytable; returns all expected results. – cp.engr Jan 19 '16 at 21:23
  • Yes the SP is server-side. I should have said USE MySchema; CALL...; (on client side). – Rick James Jan 20 '16 at 0:54
  • Is CALL used for executing stored procedures? I'm looking for a client-side only approach. – cp.engr Jan 20 '16 at 0:58
  • The construct the SQL in the client (PHP, Java, VB, whatever) and execute it. – Rick James Jan 20 '16 at 1:01
  • Only trivial SQL applications can be performed without the aid of some client language; you have reached that threshold (I think). – Rick James Jan 20 '16 at 1:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.