move all objects from schema otherschema → dbo

 USE [dbname];

GO


DECLARE @sql NVARCHAR(MAX) = N'';


SELECT @sql = @sql

  + 'ALTER SCHEMA dbo TRANSFER ' 

  + QUOTENAME(s.name) + '.' + QUOTENAME(o.name) + ';' + CHAR(13)

FROM sys.objects o

JOIN sys.schemas s ON o.schema_id = s.schema_id

WHERE s.name = 'dbname'

  AND o.type IN ('U','V','P','FN','TF','IF','TR');  -- U=table, V=view, P=proc, FN/TF/IF=functions, TR=trigger


PRINT @sql;        -- review before executing

 EXEC sp_executesql @sql;  -- uncomment to run


Comments