[MSSQL] Modify default schema DB MSSQL

exec sp_MSforeachtable "ALTER SCHEMA dbo TRANSFER ? PRINT '? modified' "
=>change schema all table

EXEC('sp_changeobjectowner @objname = ''username.tablename'', @newowner = dbo')

Hoặc có thể chạy lệnh để thay đổi schema từ tên user.table --> dbo.table

USE ngochienco;
    GO
    ALTER SCHEMA dbo TRANSFER ngochien3101.orderlist;
    GO

Change all object in database (table, procedure, view)

Part 1
    Open Microsoft SQL Server Management Studio and log in.
    Click the New Query button.
    Paste the following script into the New Query box changing oldschema to the name of the current schema:

    SELECT 'ALTER SCHEMA dbo TRANSFER ' + s.Name + '.' + o.Name
    FROM sys.Objects o
    INNER JOIN sys.Schemas s on o.schema_id = s.schema_id
    WHERE s.Name = 'oldschema'
    And (o.Type = 'U' Or o.Type = 'P' Or o.Type = 'V')

    Click Execute

This query will result in output in the Results box which looks similar to the output below:

    ALTER SCHEMA dbo TRANSFER yourschema.Table1
    ALTER SCHEMA dbo TRANSFER yourschema.Table2
    ALTER SCHEMA dbo TRANSFER yourschema.Table3
 

Part 2

    Click the New Query button.
    Paste the queries from the output of Part 1 into your new Query box
    Click Execute

This will transfer the schema name across to the new schema, also changing the stored procedures and views.

Cách 2:

/*
    Change ALL database object ownership to dbo, including;
    
        U = user table
        V = view
        s = system table
        p = stored proc
        FN = function
    
    Variables:
    
        %%DATABASENAME%%        - Database Name you're updating
        %%CURRENTOBJECTOWNERNAME%%    - CURRENT object owner name
*/

declare @object varchar(517)
declare @dbname nvarchar( 128 )

/* BE SURE TO DECLARE THE CORRECT DATABASE HERE!!!! */

SELECT @dbname = '%%DATABASENAME%%'

declare objects cursor for select name
    from sysobjects o
    where o.type in ('U','V','S','P','FN') and
    convert(sysname,user_name(o.uid)) = '%%CURRENTOBJECTOWNERNAME%%' /* this is the database-USER you are changing FROM */

open objects
    while(1=1)
    begin
    
        fetch next from objects into @object
        if @@fetch_status < 0 break
            exec ('sp_changeobjectowner ' + '''' + '%%CURRENTOBJECTOWNERNAME%%' + '.' +  @object + '''' + ', ' + '''' + 'dbo' + '''' )
    end
    
deallocate objects