How to Change schema name for all Tables in SQL Server 2005

Step 1. Check existing schema name before changing

Existing schema name is OldSchema

Step 2. Run the below script on particular database in which you want change the schema.

SELECT 'ALTER SCHEMA NewSchema TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'OldSchema'

Provide newschem name & oldschema name in highlighted in yellow colour.

Step 3. After execution the above script you will get output which containing another script to change schema for each table like below.

ALTER SCHEMA dbo TRANSFER OldSchema.Table1

ALTER SCHEMA dbo TRANSFER OldSchema.Table2

Copy entire output script and execute on the database which you want change schema for all tables

Once the script is executed successfully you will get a message as above screen.

Step  4 . Check the new schema name it is changed or not

                                                                           

Conclusion – Before changing schema name was OldSchema now it has been changed to dbo.