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:
- Right click on the table and choose modify (it's known as "Design" now)
- Around the qualities panel pick the correct possessing schema.
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
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.