How do I change db schema to dbo

Change of address: How to change schemas

Prepare for the move: Backup

Before you jump into ALTERing schemas left and right, ensure you have a complete backup of the database because mistakes happen, and the UNDO button doesn't exist here.

Compatibility first

Ensure your scripts are ready for the version of your SQL Server before you unleash them. Think of it as checking the compatibility of a new game with your old console.

Batch it up: Automate schema transfers

If you have a lot of tables to transfer (and who doesn't?), you can create ALTER SCHEMA statements for a batch transfer using a SELECT query. It's like hiring a moving company for your tables.

-- It's moving day for the tables!

SELECT 'ALTER SCHEMA dbo TRANSFER ' + TABLE_SCHEMA + '.' + TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'current_schema';


Remember the stored procedures

If you have stored procedures that reference tables changing schemas, you'll need to update these too. This is like forwarding your mail to your new address.

Execution: think before you press enter

Execute the schema changes in the correct context of your database - SQL Server isn't a mind reader! Remember to use GO to batch execute multiple ALTER SCHEMA statements together.

All in the same office: Visualising schemas

Let's simplify this process. Imagine your database objects as emojis (👩‍💼👨‍💼) in different departments (📁📁), and dbo is the central hub (🏢).

Before:    👩‍💼📁 → 👨‍💼📁
           (Workers in different departments)
After:     🏢 ← 👩‍💼👨‍💼
           (Everyone at the central hub)

Transferring schemas: Advanced scenarios

Conflict resolution: Dealing with naming conflicts

Naming clashes are not your friends. If naming conflicts arise, rename the existing dbo object or the one you're moving.

Moving more objects

To transfer multiple objects, use the DECLARE and SELECT commands in tandem. It's like driving a moving van for database objects.

-- I've got a van, let's start moving!

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql += 'ALTER SCHEMA dbo TRANSFER ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) + '; '
FROM sys.objects
WHERE schema_id = SCHEMA_ID('current_schema')

EXEC sp_executesql @sql


Post-transfer considerations

After moving, there might be some housekeeping. Update statistics or recreate indexes for optimal database performances. Review your dependencies too.

Post-schema change best practices

Always test first

Only a madman presses the big red button without understanding the consequences. Test in a non-production environment first.

Monitor and verify

Just like after moving, you should double-check if everything arrived in one piece. Monitor your database and verify function within the new dbo schema.