DECLARE @old sysname, @new sysname, @sql varchar(1000) SELECT @old = 'oldOwner_CHANGE_THIS' , @new = 'dbo' , @sql = ' IF EXISTS (SELECT NULL FROM INFORMATION_SCHEMA.TABLES WHERE QUOTENAME(TABLE_SCHEMA)+''.''+QUOTENAME(TABLE_NAME) = ''?'' AND TABLE_SCHEMA = ''' + @old + ''' ) EXECUTE sp_changeobjectowner ''?'', ''' + @new + '''' EXECUTE sp_MSforeachtable @sql
—– The same can be done to stored procedures: —–
DECLARE @oldOwner sysname, @newOwner sysname SELECT @oldOwner = 'oldOwner_CHANGE_THIS' , @newOwner = 'dbo' select 'EXECUTE sp_changeobjectowner '''+QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)+''','''+@newOwner+'''' from INFORMATION_SCHEMA.ROUTINES a where a.ROUTINE_TYPE = 'PROCEDURE' AND a.SPECIFIC_SCHEMA = @oldOwner AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(a.SPECIFIC_SCHEMA)+'.'+QUOTENAME(a.ROUTINE_NAME)), 'IsMSShipped') = 0 DECLARE @old sysname, @new sysname, @sql varchar(1000) SELECT @old = 'oldOwner' , @new = 'dbo' , @sql = ' IF EXISTS (SELECT NULL FROM information_schema.routines WHERE QUOTENAME(ROUTINE_SCHEMA)+''.''+QUOTENAME(ROUTINE_NAME) = ''?'' AND ROUTINE_SCHEMA = ''' + @old + ''' ) EXECUTE sp_changeobjectowner ''?'', ''' + @new + '''' EXECUTE sp_MSforeachtable @sql