I've got a table that experienced the "db_owner" schema, and that i require it within the "dbo" schema.

It is possible to script or command to operate to change it over?

ALTER SCHEMA NewSchama TRANSFER dbo.Table1

In SQL Server Management Studio:

  1. Right click on the table and choose modify (it's known as "Design" now)
  2. Around the qualities panel pick the correct possessing schema.

Show all TABLE_SCHEMA with this choose:

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 

This can be used query to alter all schema for those tables to dbo table schema:

DECLARE cursore CURSOR FOR 

SELECT TABLE_SCHEMA, TABLE_NAME 
FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA <> 'dbo' 


DECLARE @schema sysname, 
 @tab sysname, 
 @sql varchar(500) 


OPEN cursore     
FETCH NEXT FROM cursore INTO @schema, @tab 

WHILE @@FETCH_STATUS = 0     
BEGIN 
 SET @sql = 'ALTER SCHEMA dbo TRANSFER ' + @schema + '.' + @tab     
 PRINT @sql     
 FETCH NEXT FROM cursore INTO @schema, @tab     
END 

CLOSE cursore     
DEALLOCATE cursore

simple answer

sp_changeobjectowner [ @objname = ] 'object' , [ @newowner = ] 'owner'

you don't have to stop all connections towards the database, you can do this quickly.

After I use SQL Management Studio I don't obtain the 'Modify' option, only 'Design' or 'Edit'. For those who have Visual Studio (I've checked Versus.Internet 2003, 2005 &lifier 2008) you should use the Server Explorer to alter the schema. Right click the table and choose 'Design Table' (2008) or 'Open Table Definition' (2003, 2005). Highlight the entire "Column Title" column. After that you can right click and choose 'Property Pages' or Qualities (2008). In the property sheet you need to begin to see the 'Owner' (2003 &lifier 2005) or 'Schema' (2008) having a drop lower list for possible schemas.

Only Use This

ALTER SCHEMA dbo TRANSFER db_owner.Tablename

I personally use this for situations where a lot of tables have to be inside a different schema, within this situation the dbo schema.

declare @sql varchar(8000)
;

select
  @sql = coalesce( @sql, ';', '') + 'alter schema dbo transfer [' + s.name + '].[' + t.name + '];'
from 
  sys.tables t
  inner join
  sys.schemas s on t.[schema_id] = s.[schema_id]
where 
  s.name <> 'dbo'
;

exec( @sql )
;

You have to first of all stop all connections towards the database, alter the possession from the tables which are 'db_owner' by running the command

sp_MSforeachtable @command1="sp_changeobjectowner ""?"",'dbo'"

where ? may be the table title.