2

I have a set of database schemas, with each one having the same set of tables.

I want to alter a table by adding a column in each one of the schemas with a single command, instead of doing it for every schema separateley.

Is that possible?

Dimitris Papazacharias
  • 661
  • 1
  • 7
  • 21
  • 1
    No. You could use dynamic sql and a stored procedure as suggested in William's answer; but for a one time `Alter`, that seems like a bit of overkill to me. ... and to make such an proc implementation more reusable, you'd need to devise a mechanism for identifying which tables in which schemas should get the Alter, and that handles different kinds of alters appropriately. – Uueerdo Feb 07 '20 at 17:26

1 Answers1

1

This isn't quite identical to Querying multiple databases at once but it's similar. As in the linked question, you could write a procedure:

  1. Get the schema names from information_schema.tables
  2. Create your query template
  3. Loop over the schema names and for each schema sub the schema name in the query template
geco17
  • 5,152
  • 3
  • 21
  • 38
  • Could you please tell us the best solution for the above problem or the solution that you have implemented? – paawan Sep 03 '21 at 14:45