alter table script on all my databases

ExEEEEEEE 1 Reputation point
2022-02-01T09:02:06.967+00:00

There´s almost 300 databases in the server, almost all of them have the same structure (same tables and columns), well, I need to add 2 more columns to the clients table on every single one of them... I´ve been through several posts about different sintaxys and i'm not getting it at all...
¿Is there any example or tutorial where can I get some help?

I'll apreciate any help of you all

Also, there's 3 types of databases in here: the A, B and Cs... and I just need to alter the A & B types...

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,714 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Erland Sommarskog 101K Reputation points MVP
    2022-02-01T22:49:33.703+00:00

    I have a section on this in my article on dynamic SQL that you can use as a starting point: https://www.sommarskog.se/dynamic_sql.html#alldatabases.

    0 comments
  2. YufeiShao-msft 7,056 Reputation points
    2022-02-02T07:26:19.277+00:00

    Hi @ExEEEEEEE ,

    Try to use sp_msforeachdb, it allows us to execute a T-sql statement against every database in the current SQL Server instance
    https://www.c-sharpcorner.com/UploadFile/63f5c2/sp_msforeachtable-stored-procedure-in-sql-server-2012/
    But it is strongly recommended to avoid using undocumented features of SQL Server in your Production environment.
    https://social.technet.microsoft.com/wiki/contents/articles/16976.aspx

    Add columns in multiple databases

    -------------

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments