Changing the Schema for all Tables in a Tables - MS SQL Server 2008 R2.

Hi EE,

I know to change the schema name of table you the following command:

ALTER SCHEMA MySchema TRANSFER dbo.MyTable

My query are there any scripts out there that do this to all the tables in a database as this database has hundreds of tables?

Any assistance is appreciated.

Thank you.

Microsoft SQL ServerSQL

Last Comment

Zack

8/22/2022 - Mon

Kelvin Sparks

4/6/2017

I'd create a loop based on the tables names returned from something like SELECT Name from sys.Tables and then generate dynamic sql like

'ALTER SCHEMA myschema TRANSFER ' + dbo.Mytable

then execute it.

Sorry, not at a computer to code it exactly.

Kelvin

ASKER CERTIFIED SOLUTION

JesterToo

4/6/2017

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Zack