MSSQL: 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 while we can meet different approaches in real life...
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
Friday, December 5, 2003 5:40 PM
