T-SQL to change schema/owner name in Sql Server

  

Syntax for changing owner name or schema of database objects such as stored procedures, functions, and views is

	exec sys.sp_changeobjectowner <<object name>>, <<new owner name>>

The object name(@objname) parameter should in the format "[owner].[object]". The new owner name(@newowner) should be valid name from

	select * from sysusers

The following sql stores list of user created table with name and its schema or owner name

	select t.[name] as TableName,t.object_id,s.[name] as OwnerName 
	from sys.tables t  inner join sys.schemas s
	on s.schema_id=t.schema_id where t.is_ms_shipped=0

The following example changes the table name "CityStateMaster" of "dbo" schema to "guest" schema.

	sp_changeobjectowner 'guest.CityStateMaster',dbo

The following sql can be used to generate sqls to convert schema or owner of all user created functions and stored procedures to 'dbo' owner.

SELECT 'exec sp_changeobjectowner ''' + QUOTENAME(ROUTINE_SCHEMA) + '.' +
QUOTENAME(ROUTINE_NAME) + ''',' + 'dbo;' FROM INFORMATION_SCHEMA.ROUTINES
WHERE OBJECTPROPERTY(OBJECT_ID(ROUTINE_NAME),'IsMSShipped') = 0