How to Change Db Schema to Dbo

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];

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

Change database Schema name in database project vs2012

My questions is to changes existing schema name from dbo to something else.

I finally find a easy way to do it.

I used the sql schema compare in vs 2012 .

1) change the actually database schema.
2) select the actually database as the source and and select my database project as target.
3) do a update. it will update the database project.

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.

Cleanest way to change database schema in EF6

With fluent mappings in Entity Framework code-first you can indicate the default schema at runtime. This is one statement in OnModelCreating in your DbContext subclass, for instance:

modelBuilder.HasDefaultSchema("dev");

You're used to regenerating the model from the database, from which I conclude that the model doesn't contain many (or any) customizations that would make model generation a painstaking operation. This also should make it relatively easy to move to code-first. So I'd recommend you do that.

In Visual Studio, you can generate a code-first model from an existing database by adding an "ADO.Net Entity Data Model" from the templates that come with Entity Framework tools for Visual Studio. (Probably pre-installed). Choose the option "Code First from database" and follow the guidelines.

If you do that, you'll find a connection string in the project containing the model. This connection string may serve as template for the connection string you will put in the config file of your executing assembly. You'll notice that it doesn't look like...

metadata=res://* ... provider=System.Data.SqlClient;provider connection string="...""

This is the connection string that belongs to a database-first edmx model. It contains a path to the metadata files that are generated as resources into the assembly. Instead, the connection string will be a simple ADO.Net connection string. With code-first, EF will generate the meta data at runtime.

If you have this, you can add an entry in your config file for the default database schema and use that to set the schema as I showed above.

changing schema from `dbo` to custom name

Applies to: SQL Server (SQL Server 2008 through current version), Azure SQL Database.

To transfer a securable from another schema, the current user must have CONTROL permission on the securable (not schema) and ALTER permission on the target schema.

If the securable has an EXECUTE AS OWNER specification on it and the owner is set to SCHEMA OWNER, the user must also have IMPERSONATION permission on the owner of the target schema.

All permissions associated with the securable that is being transferred are dropped when it is moved.

ALTER SCHEMA can only be used to move securables between schemas in the same database. To change or drop a securable within a schema, use the ALTER or DROP statement specific to that securable.

If a one-part name is used for securable_name, the name-resolution rules currently in effect will be used to locate the securable.

All permissions associated with the securable will be dropped when the securable is moved to the new schema. If the owner of the securable has been explicitly set, the owner will remain unchanged. If the owner of the securable has been set to SCHEMA OWNER, the owner will remain SCHEMA OWNER; however, after the move SCHEMA OWNER will resolve to the owner of the new schema. The principal_id of the new owner will be NULL.

Maybe you should just script out the table you want to move and recreate it in the target dbo?

How do I move a table into a schema in T-SQL

ALTER SCHEMA TargetSchema 
    TRANSFER SourceSchema.TableName;

If you want to move all tables into a new schema, you can use the undocumented (and to be deprecated at some point, but unlikely!) sp_MSforeachtable stored procedure:

exec sp_MSforeachtable "ALTER SCHEMA TargetSchema TRANSFER ?"

Ref.: ALTER SCHEMA

SQL 2008: How do I change db schema to dbo

---

Related Topics