Change Schema Name Of Table In SQL

问题:

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?

回答1:

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

回答2:

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

回答3:

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.

回答4:

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 

回答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

回答6:

CREATE SCHEMA exe AUTHORIZATION [dbo]
GO

ALTER SCHEMA exe
TRANSFER dbo.Employees
GO

回答7:

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

回答8:

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'

回答9:

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.

回答10:

To create a New Schema, follow the below steps:

1. Open your Database. 2. Route to Security folder. 3. Inside it is a folder named Schema. 4. Right click the schema folder> New Schema. 5. Add a Schema > Click OK.

To alter a schema, run the following sql command:

ALTER SCHEMA new_schema TRANSFER dbo.Employees

回答11:

In case, someone looking for lower version -

For SQL Server 2000:

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