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

Zack
Zack used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
ZackGeneral IT Goto Guy

Author

Commented:
Thank you very much for the link.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial