CODEWITHSUNDEEP
sqlsql-serverschemaalter
Chagbert
Chagbert

Reputation: 758

SQL alter schema multiple tables at once

I recently upsized my MS-Access database to SQL Server and in the process successfully exported a bunch of tables.

However, now the imported tables are prefixed with MSH-CHAMBERS\mfanimpela which I assume is my username and this is the schema (or owner property).

While I have seen posts on changing EACH table schema to the desired 'dbo', I want a statement that can help me change ALL of my tables (since these are so many).

Please help - chagbert.

Upvotes: 0

Views: 989

Answers (2)

Kejsi Struga
Kejsi Struga

Reputation: 560

Use the sp_MSForEachTable procedure like this:

EXEC **sp_MSForEachTable** 'ALTER SCHEMA dbo TRANSFER ?'

-- in the above example dbo is the targeted schema where you want to place your tables

Upvotes: 1

SteveB
SteveB

Reputation: 1514

Google sp_MSForEachTable and use it to call sp_rename

You might need to do additional checks before you rename the table to avoid mistakes.

Upvotes: 0

Related Questions