Change tables owner to dbo with sp_changeobjectowner

Sometimes we need to change all tables in the database to be owned by dbo for some maintenance purpose or to fix up some accidental errors. The following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. It uses sp_changeobjectowner system stored procedure:

DECLARE tabcurs CURSOR

FOR

    SELECT 'SOMEOWNER.' + [name]

      FROM sysobjects

     WHERE xtype = 'u'

OPEN tabcurs

DECLARE @tname NVARCHAR(517)

FETCH NEXT FROM tabcurs INTO @tname

WHILE @@fetch_status = 0

BEGIN

    EXEC sp_changeobjectowner @tname, 'dbo'

    FETCH NEXT FROM tabcurs INTO @tname

END

CLOSE tabcurs

DEALLOCATE tabcurs