/*

select * from sys.database_principals

*/


begin -- #6709
    declare @schema sysname
           , @name  sysname
           , @sql   nvarchar(4000)
    
    declare cr cursor fast_forward 
    for
        select TABLE_SCHEMA
              ,TABLE_NAME
          from INFORMATION_SCHEMA.TABLES 
         WHERE TABLE_SCHEMA != 'dbo'
         
         
    open cr
    while (1=1) begin
            
        fetch next from cr into
                                 @schema 
                               , @name        
    
        if @@fetch_status != 0 break
        
        IF EXISTS 
           ( 
                select * 
                  from INFORMATION_SCHEMA.TABLES  IST
                 where IST.TABLE_SCHEMA = 'dbo'
                   and TABLE_NAME       = @name
                   and TABLE_TYPE       = 'BASE TABLE' 
            )
        begin
            -- 6709: the correspondent table already exists under [dbo] table as well
            -- drop the other-schema-table
            
            set @sql = 'drop table [' + @schema + '].[' + @name + ']'
        
        end
        else begin

            set @sql = 'alter schema [dbo]  transfer [' + @schema + '].[' + @name + ']'
        
        end

        print @sql
        exec(@sql)

    end
    close cr
    deallocate cr
               
    

end
go


begin

	declare @username sysname
	       ,@sql nvarchar(4000)

	declare cr cursor local fast_forward 
	for
	select name 
	 from   sys.database_principals
	 where  type in ('U', 'S')
	   and default_schema_name is not null
	   and  default_schema_name  != 'dbo'



	 open cr
	 while (1 = 1) begin

		fetch next from cr into @username

		if @@Fetch_Status <> 0 break

		set @sql = 'ALTER USER [' + @username + '] with default_schema = [dbo]'

		print @sql
		exec (@sql)

	 end
	 close cr
	 deallocate cr

end
