Rename SQL Server Schema

Rename SQL Server Schema name

You could probably move all objects from 'MySchema' to a temporary schema say 'tmp_schema' using

ALTER SCHEMA NewSchema TRANSFER OldSchema.Object;

Then drop 'MySchema', recreate as 'myschema' and then move all the objects back with the same method. This post has a script to do it that you might find useful.

Rename SQL Server Schema

Change Schema Name Of Table In SQL

Create Schema :

IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = 'exe')) 
BEGIN
    EXEC ('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END

ALTER Schema :

ALTER SCHEMA exe 
    TRANSFER dbo.Employees

How do I quickly rename a MySQL database (change schema name)?

For InnoDB, the following seems to work: create the new empty database, then rename each table in turn into the new database:

RENAME TABLE old_db.table TO new_db.table;

You will need to adjust the permissions after that.

For scripting in a shell, you can use either of the following:

mysql -u username -ppassword old_db -sNe 'show tables' | while read table; \ 
    do mysql -u username -ppassword -sNe "rename table old_db.$table to new_db.$table"; done

OR

for table in `mysql -u root -ppassword -s -N -e "use old_db;show tables from old_db;"`; do mysql -u root -ppassword -s -N -e "use old_db;rename table old_db.$table to new_db.$table;"; done;

---

Notes:

• There is no space between the option -p and the password. If your database has no password, remove the -u username -ppassword part.

• If some table has a trigger, it cannot be moved to another database using above method (will result Trigger in wrong schema error). If that is the case, use a traditional way to clone a database and then drop the old one:

  mysqldump old_db | mysql new_db

• If you have stored procedures, you can copy them afterwards:

  mysqldump -R old_db | mysql new_db

Side effects of database and schema rename

A rename operation is simply a metadata change. There is no impact in terms of costs in renaming a schema.

The only impact will be on SQL queries that were explicitly using the old schema name will break. You may want to double-check that permissions on the schema are what you expect too.

Rather than renaming the public schema why not just clone it to a schema named production instead? That way you get to keep the public schema, which is always created with a new database. Even this will have no impact on costs despite there being two schemas with the same data (unless you make a bunch of modifications in both due to time travel/failsafe storage). see cloning documentation

How to change schema of all tables, views and stored procedures in MSSQL

Yes, it is possible.

To change the schema of a database object you need to run the following SQL script:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.ObjectName

Where ObjectName can be the name of a table, a view or a stored procedure. The problem seems to be getting the list of all database objects with a given shcema name. Thankfully, there is a system table named sys.Objects that stores all database objects. The following query will generate all needed SQL scripts to complete this task:

SELECT 'ALTER SCHEMA NewSchemaName TRANSFER [' + SysSchemas.Name + '].[' + DbObjects.Name + '];'
FROM sys.Objects DbObjects
INNER JOIN sys.Schemas SysSchemas ON DbObjects.schema_id = SysSchemas.schema_id
WHERE SysSchemas.Name = 'OldSchemaName'
AND (DbObjects.Type IN ('U', 'P', 'V'))

Where type 'U' denotes user tables, 'V' denotes views and 'P' denotes stored procedures.

Running the above script will generate the SQL commands needed to transfer objects from one schema to another. Something like this:

ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CONTENT_KBArticle;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_Analytics_Statistics_Delete;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Proc_CMS_QueryProvider_Select;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.COM_ShoppingCartSKU;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.CMS_WebPart;
ALTER SCHEMA NewSchemaName TRANSFER OldSchemaName.Polls_PollAnswer;

Now you can run all these generated queries to complete the transfer operation.

How to rename a table in SQL Server?

To rename a table in SQL Server, use the sp_rename command:

exec sp_rename 'schema.old_table_name', 'new_table_name'

How do I change db schema to dbo

ALTER SCHEMA dbo TRANSFER jonathan.MovieData;

See ALTER SCHEMA.

Generalized Syntax:

ALTER SCHEMA TargetSchema TRANSFER SourceSchema.TableName; 

---

Related Topics