T-SQL: Change tables owner to dbo with sp_changeobjectowner

Sometimes there is a need to change all tables in the database to be owned by dbo for maintenance or to fix up accidental errors. All tables owned by dbo schema is usually best practices in the database application development with MSSQL.

The following small SQL code snippet goes through all user tables in the database and changes their owner to dbo. It usessp_changeobjectowner system stored procedure

DECLARE tabcurs CURSOR
FOR
    SELECT 'dips.' + [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

Hope this is useful..