I want to change schema name of table "Employees"in Database. In the current table Employees database schema name is "Schemaconverted" I want to change it to dbo.
Example:
FROM
Schemaconverted.Employees
TO
dbo.Employees
Syntax
ALTER SCHEMA dbo
TRANSFER Schemaconverted.Employees
syntax for All Base tables
use TestDB
go
declare @sql varchar(8000), @table varchar(1000), @oldschema varchar(1000), @newschema varchar(1000)
set @oldschema = 'Schemaconverted'
set @newschema = 'dbo'
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
No comments:
Post a Comment