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.
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.
Do more with
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
'ALTER SCHEMA myschema TRANSFER ' + dbo.Mytable
then execute it.
Sorry, not at a computer to code it exactly.
Kelvin
Here is a link that shows how to do it in SSMS...
https://support.managed.com/kb/a100/how-to-change-schema-of-mssql-tables-stored-procedures-and-views-all-at-the-same-time.aspx
https://support.managed.com/kb/a100/how-to-change-schema-of-mssql-tables-stored-procedures-and-views-all-at-the-same-time.aspx
Premium Content
You need an Expert Office subscription to comment.Start Free Trial