Change Schema Name Of Table In SQL
SqlSql ServerTsqlSchemaSql Problem Overview
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?
Sql Solutions
Solution 1 - 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
Solution 2 - Sql
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.
Solution 3 - Sql
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
Solution 4 - Sql
CREATE SCHEMA exe AUTHORIZATION [dbo]
GO
ALTER SCHEMA exe
TRANSFER dbo.Employees
GO
Solution 5 - Sql
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
Solution 6 - Sql
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
Solution 7 - Sql
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...
https://stackoverflow.com/questions/1149159/how-do-i-move-a-table-into-a-schema-in-t-sql
Solution 8 - Sql
In case, someone looking for lower version -
For SQL Server 2000:
sp_changeobjectowner @objname = 'dbo.Employess' , @newowner ='exe'
Solution 9 - Sql
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.
Solution 10 - Sql
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'
Solution 11 - Sql
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