Databases

How to change sql server database schema to dbo

Databases > MS SQL 2012

Please excute the query below with your sql server management studio to change your sql server database schema to dbo
  DECLARE cursore CURSOR FOR     select specific_schema as 'schema', specific_name AS 'name' FROM INFORMATION_SCHEMA.routines WHERE specific_schema <> 'dbo'   UNION ALL   SELECT TABLE_SCHEMA AS 'schema', TABLE_NAME AS '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    exec (@sql)   FETCH NEXT FROM cursore INTO @schema, @tab     END   CLOSE cursore     DEALLOCATE cursore