1

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.

2
  • 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, 2016 at 21:24
  • @Kassandry, I added the info to the question, thanks.
    – cp.engr
    Jan 14, 2016 at 21:28

2 Answers 2

1

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.

1
  • 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, 2016 at 1:12
0

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;
5
  • (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, 2016 at 21:23
  • Yes the SP is server-side. I should have said USE MySchema; CALL...; (on client side).
    – Rick James
    Jan 20, 2016 at 0:54
  • Is CALL used for executing stored procedures? I'm looking for a client-side only approach.
    – cp.engr
    Jan 20, 2016 at 0:58
  • The construct the SQL in the client (PHP, Java, VB, whatever) and execute it.
    – Rick James
    Jan 20, 2016 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, 2016 at 1:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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