CODEWITHSUNDEEP
sql-serverviewsql-server-2012
JAN
JAN

Reputation: 21865

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

I created a new view that have a lot of joins between multiple tables , under SQL Server 2012 Management.

The view was created under the name dbo.vw_clientsTransactions .

How can I change the DB schema from dbo.vw_clientsTransactions , to CL.vw_clientsTransactions ?

Thanks

Upvotes: 5

Views: 14169

Answers (3)

Ben Thul
Ben Thul

Reputation: 32697

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

Upvotes: 10

JAN
JAN

Reputation: 21865

OK , now I know how :

1.Create your view , save it as whatever name comes to mind .

2.Right click on the view : Script View as => Create To => New Query Editor Windows .

3.Now a window with the script for creating the view , would be presented on the screen .

4.Change the dbo to CL (in my case) , and then run the query (F5 / Execute) and you're done .

Upvotes: 0

rodrigogq
rodrigogq

Reputation: 1953

You have to delete this view and, on Create, specify your schema in the view name!

Upvotes: 3

Related Questions