How to change Schema from DBO to user created schema for all objects

Move all database objects from DBO schema to user created (PRD in below queries) schema.

Recently i got a request to change the dbo schema to user created schema PRD.

So please find the below steps to do the changes.

Ex: PRD is the schema name

Please take database backup before doing the schema changes.

Step1:

Please check if user defined Schema already exists:

select*fromsys.schemas

ifuser defined schema doesn’t exist in your database.

CREATESCHEMA [PRD] AUTHORIZATION [DBUser]

·         PRD is user choice schema name

·         DBuser is nothing but login who will be default owner.

Step2:

Query to check no of objects having schema  PRD or DBO.

selectcount(1)as dbo_count

fromsys.objects

whereschema_id=schema_id('dbo')

andtypein('U','V','P','FN')

selectcount(1)as [PRD]

fromsys.objects

whereschema_id=schema_id('[PRD]')

andtypein('U','V','P','FN')

Step3:

Please make sure DB user has default schema PRD

ALTERUSER [DBUser]

WITHDEFAULT_SCHEMA= [PRD]

Step4:

If you are moving the all objects havingDBO schema  toPRD schema.

Script1:

Below Script is used to change schema for only objects

declare @theSchema sysname

declare @oneObj sysname, @aSQL nvarchar(1000)

set @theSchema =quotename('PRD')

-- migration of user-defined types in [dbo]

declare objCur cursorfor

selectquotename([name])

fromsys.objects

where (schema_id=schema_id('dbo')orschema_id=schema_id(@theSchema))

andtypein('R','D')-- Rules and Defaults

open objCur

fetchfrom objCur into @oneObj

while@@fetch_status=0 begin

set @aSQL ='alter schema '+@theSchema+' transfer [dbo].'+@oneObj

print @aSQL

execsp_executeSQL@aSQL

fetchnextfrom objCur into @oneObj

end

close objCur

deallocate objCur

declare @oneType varchar(50)

declare typCur cursorfor

select name fromsys.types

where is_user_defined = 1

andschema_id<>schema_id(@theSchema)

print'Beginning migration'

open typCur

fetchfrom typCur into @oneType

while@@fetch_status= 0 begin

exec('alter schema '+ @theSchema +' transfer TYPE::'+@oneType)

print'Moved '+@oneType

fetchnextfrom typCur into @oneType

end

close typCur

deallocate typCur

-- confirm by examining the schema_id field

select*fromsys.typeswhere is_user_defined = 1

Script2:

Below Script is used to change schema for only objects

declare @theSchema sysname

declare @oneObj sysname, @aSQL nvarchar(1000)

set @theSchema =quotename('PRM')

-- migration of user-defined objects in [dbo]

declare objCur cursorfor

selectquotename([name])

fromsys.objects

where (schema_id=schema_id('dbo')orschema_id=schema_id(@theSchema))

andtypein('U','V','P','FN')

open objCur

fetchfrom objCur into @oneObj

while@@fetch_status=0 begin

set @aSQL ='alter schema '+@theSchema+' transfer [dbo].'+@oneObj

print @aSQL

execsp_executeSQL@aSQL

fetchnextfrom objCur into @oneObj

end

close objCur

deallocate objCur

-- confirm by looking at the former and current schemas

select*fromsys.objects

whereschema_id=schema_id(@theSchema)

orschema_id=schema_id('dbo')

orderbyschema_id

To verify if still any objects are not moved from DBO schema to user PRD schema, please use the below query.

select*fromsys.objects

whereschema_id=schema_id(quotename('PRM'))

orschema_id=schema_id('dbo')

orderbyschema_id

Please use the below query to move the objects that have not moved by above query.

alterschemaPRDtransfer [dbo].objectname