Change Schema Name of Table in SQL

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 to change schema name of a table in all stored procedures

Get a list of dependent objects by right-clicking on the table before you change the schema and then look at what is dependent on the table, make a list and then change those. There is, however, always a possibility that you'll miss something because it is possible to break the dependencies SQL server tracks.

But the best way would be to script the database out into a file and then do a search for the table name, make a list of all of the sprocs where it needs to be changed and then add those to the script to change the schema of the table.

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 do I create a SQL table under a different schema?

1. Right-click on the tables node and choose New Table...
2. With the table designer open, open the properties window (view -> Properties Window).
3. You can change the schema that the table will be made in by choosing a schema in the properties window.

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; 

How do I change DB schema from dbo to another schema?

To change the schema of any object, use the following syntax:

alter schema [new_schema] transfer [old_schema].[object_name];

So, in your case, you'd do:

alter schema [CL] transfer [dbo].[vw_clientsTransactions];

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

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

How to set schema for entire query

We can set a different default schema using alter session:

alter session set current_schema = SCOTT;

This means we can reference objects in SCOTT's schema without prefixing them with SCOTT. That is, instead of scott.emp we can just:

select * from emp;

Note that we now need to prefix objects in our own schema:

select * from my.table_1;

Also, in case it isn't clear, setting current_schema doesn't confer any privileges: if SCOTT hasn't granted INSERT on EMP or SELECT on SALGRADE to us we can't use this technique to gain elevated access.

---

Related Topics