Change Schema Name Of Table In SQL

Question

I want to change schema name of table Employees in Database. In the current table Employees database schema name is dbo I want to change it to exe. How can I do it ?

Example:

FROM

dbo.Employees

TO

exe.Employees

I tried with this query:

ALTER SCHEMA exe TRANSFER dbo.Employees

But this gives me an error:

Cannot alter the schema 'exe', because it does not exist or you do not have permission.

What did I miss?

Have you [created the exe schema](http://msdn.microsoft.com/en-us/library/ms189462.aspx)? — PinnyM, Mar 18 '13 at 17:15
run `create schema`: http://msdn.microsoft.com/en-US/library/ms189462%28v=sql.90%29.aspx — , Mar 18 '13 at 17:35
I saw this article but it is little confusing. Can you show me how to create the schema in my situation ? — theChampion, Mar 18 '13 at 17:40

score 368 · Accepted Answer · edited Jun 10 '15 at 19:02

368

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

edited Jun 10 '15 at 19:02

Mike G

4,232
9
40
66

answered Mar 18 '13 at 17:32

Pandian

8,848
2
23
33

3

Can you explain what is sys.scemas and EXEC(...) function ? – theChampion Mar 18 '13 at 17:55
11

`sys.schemas` is a table that contains all the schemas for the database. The Exec('...') just runs a dynamic SQL call, in this case it is needed because a CREATE SCHEMA command must be the first statement in a query batch and executing as dynamic SQL gets you this. – Eric J. Price Mar 18 '13 at 18:03
2

For **all Tables** , check [this](http://stackoverflow.com/a/1149168/2218697) and [this](http://stackoverflow.com/a/10943302/2218697) to do it in single statement, hope helps some one. – Shaiju T Jul 03 '16 at 09:45
1

Using the alter schema seems extremely slow. (I stopped it after 3 minutes for transfer a small table with 300 rows.) Instead, I used select * into exe.Employees from dbo.Employees – fivelements Jan 21 '19 at 21:44
For empty tables run fast without any problem. – Dany Jun 21 '22 at 10:16

score 57 · Answer 2 · edited Sep 25 '15 at 20:25

57

ALTER SCHEMA NewSchema TRANSFER [OldSchema].[TableName]

I always have to use the brackets when I use the ALTER SCHEMA query in SQL, or I get an error message.

edited Sep 25 '15 at 20:25

Ihor Kaharlichenko

5,944
1
26
32

answered Sep 25 '15 at 20:02

Lauren

589
4
2

Adding square-brackets to the table name, table schema, and/or destination schema did not resolve the issue for me. – Dai Dec 09 '21 at 06:07

score 34 · Answer 3 · answered Mar 18 '13 at 17:20

34

Try below

declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema   varchar(1000)

  set @oldschema = 'dbo'
  set @newschema = 'exe'

 while exists(select * from sys.tables where schema_name(schema_id) = @oldschema)

  begin
      select @table = name from sys.tables 
      where object_id in(select min(object_id) from sys.tables where  schema_name(schema_id)  = @oldschema)

    set @sql = 'alter schema ' + @newschema + ' transfer ' + @oldschema + '.' + @table

   exec(@sql)
 end

answered Mar 18 '13 at 17:20

DevelopmentIsMyPassion

3,541
4
34
60

1

I had to use `set @sql = 'alter schema [' + @newschema + '] transfer [' + @oldschema + '].' + @table` to make sure some special words/characters does not cause error. – Stoinov Nov 15 '18 at 11:56
This piece of sql will transfer all tables with the "dbo" schema to the "exe" schema. I used it to transfer a custom user schema to "dbo" for development. – Wasted_Coder Jun 20 '22 at 13:40

score 16 · Answer 4 · edited Jan 14 '19 at 08:04

16

CREATE SCHEMA exe AUTHORIZATION [dbo]
GO

ALTER SCHEMA exe
TRANSFER dbo.Employees
GO

edited Jan 14 '19 at 08:04

Flexo

87,323
22
191
272

answered Dec 02 '15 at 05:59

Dilip Kr Singh

1,418
1
18
26

Jason D. · Answer 5 · 2014-10-15T22:47:05.000

Through SSMS, I created a new schema by:

Clicking the Security folder in the Object Explorer within my server,
right clicked Schemas
Selected "New Schema..."
Named my new schema (exe in your case)
Hit OK

I found this post to change the schema, but was also getting the same permissions error when trying to change to the new schema. I have several databases listed in my SSMS, so I just tried specifying the database and it worked:

USE (yourservername)  
ALTER SCHEMA exe TRANSFER dbo.Employees

score 5 · Answer 6 · edited Oct 08 '14 at 11:51

5

Your Code is:

FROM
 dbo.Employees
TO
 exe.Employees

I tried with this query.

ALTER SCHEMA exe TRANSFER dbo.Employees

Just write create schema exe and execute it

edited Oct 08 '14 at 11:51

Piotr Kula

9,597
8
59
85

answered Jul 19 '13 at 13:17

user2599599

61
1
1

score 3 · Answer 7 · edited May 23 '17 at 11:47

3

Check out MSDN...

CREATE SCHEMA: http://msdn.microsoft.com/en-us/library/ms189462.aspx

Then

ALTER SCHEMA: http://msdn.microsoft.com/en-us/library/ms173423.aspx

Or you can check it on on SO...

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

edited May 23 '17 at 11:47

Community

1
1

answered Mar 18 '13 at 17:18

Chains

12,541
8
45
62

Can you show me example of how to create the schema in my situation ? – theChampion Mar 18 '13 at 17:46

score 2 · Answer 8 · answered Dec 03 '15 at 18:55

2

Make sure you're in the right database context in SSMS. Got the same error as you, but I knew the schema already existed. Didn't realize I was in 'MASTER' context. ALTER worked after I changed context to my database.

answered Dec 03 '15 at 18:55

billmack30

23
1
3

score 2 · Answer 9 · answered Nov 22 '18 at 06:50

2

In case, someone looking for lower version -

For SQL Server 2000:

sp_changeobjectowner @objname = 'dbo.Employess' , @newowner ='exe'

answered Nov 22 '18 at 06:50

iravinandan

659
6
16

score 0 · Answer 10 · answered Mar 18 '13 at 17:17

0

Be very very careful renaming objects in sql. You can cause dependencies to fail if you are not fully away with what you are doing. Having said that this works easily(too much so) for renaming things provided you have access proper on the environment:

exec sp_rename 'Nameofobject', 'ReNameofobject'

answered Mar 18 '13 at 17:17

djangojazz

14,131
10
56
94

2

Unfortunately this won't work in this scenario, sp_rename only works for changing the [name] value of an object. You can't change the schema with it. If you tried `exec sp_rename 'dbo.Employees', 'exe.Employees'` you would get the name [dbo].[exe.Employees] – Eric J. Price Mar 18 '13 at 17:24
1

ALTER SCHEMA (Name of Schema) TRANSFER (schemaName).(ObjectName); – djangojazz Mar 18 '13 at 17:53
Also note that when you change the schema of a table, any Views that use that table *won't* get updated. You'll need to manually update the text (the schema names) in these views. (Sigh...) – Mike Gledhill Aug 16 '17 at 08:41

score 0 · Answer 11 · answered Dec 13 '21 at 20:10

0

also you can transfer your data from default schema 'dbo' to your schema from wizard by 1-double click on db diagram 2- right click on your certian entity --> select properties 3- on right at identity , change the schema name

answered Dec 13 '21 at 20:10

moustafa

1

score 0 · Answer 12 · answered Nov 28 '22 at 09:30

0

For SQL2019:

ALTER SCHEMA [SCEMAYOUWANTTOTRANSFERTO] TRANSFER [CURRENTSCHEMA].[TABLENAME];

Example:

ALTER SCHEMA [dbo] TRANSFER [Curated].[MyTable];

answered Nov 28 '22 at 09:30

Attie Wagner

1,312
14
28

Change Schema Name Of Table In SQL

12 Answers12

Linked