TRY CATCH locking up sysobjects table

[123FakeSt]

I wrote this little script to change all of a schema's tables to 'dbo' but when it runs into a table that already has a 'dbo' version, it locks up my entire database until I kill the process.

Is there an easier way to acheive this?

DECLARE @dbusername		VARCHAR(255)
DECLARE @tablename		VARCHAR(255)
DECLARE @fullname		VARCHAR(255)

SET @dbusername = 'notdbo'

/* Get all tables with that username */
IF OBJECT_ID('tempdb..#tables') IS NOT NULL DROP TABLE #tables
IF OBJECT_ID('tempdb..#results') IS NOT NULL DROP TABLE #results
SELECT [name] stablename INTO #tables FROM sysobjects WHERE USER_NAME(uid) = @dbusername AND xtype = 'U'
SELECT 'Step 1 - ' + stablename + ' found for user ' + @dbusername AS sresult INTO #results FROM #tables
SELECT TOP 1 @tablename = stablename FROM #tables
WHILE @@rowcount > 0
BEGIN
	SET @fullname = '[' + @dbusername + '].[' + @tablename + ']' 
	INSERT INTO #results SELECT 'Step 2 - ' + @fullname + ' - Attempting to change to dbo'
	BEGIN TRY
		EXEC sp_changeobjectowner @fullname,'dbo'
	END TRY
	BEGIN CATCH
		INSERT INTO #results SELECT 'Step 2 - ' + @fullname + ' ERROR:' + ERROR_MESSAGE()
	END CATCH
	DELETE FROM #tables WHERE stablename = @tablename
	SELECT TOP 1 @tablename = stablename FROM #tables
END

SELECT sresult FROM #results ORDER BY 1

The early bird gets the worm, but the second mouse gets the cheese.

 

[SQLBill]

Validate the schema in the beginning. You want to change all objects that are not already owned by DBO to DBO.

DBO is usually schema_id = 1

SELECT *
FROM sys.schemas

You can join on the sys.schemas table to elimate objects already owned by DBO. Try something like this (not tested).

SELECT o.[name]
FROM sys.objects o
JOIN sys.schemas s
ON o.schema_id = s.schema_id
WHERE s.name <> 'dbo'

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[123FakeSt]

I actually need the script to only change one schema to dbo (this is a SQL Server 2000 upgrade to 2008 issue)
Well the issue is here:

CRASHES SERVER UNTIL PROCESS KILLED

	BEGIN TRY
		EXEC sp_changeobjectowner @fullname,'dbo'
	END TRY
	BEGIN CATCH
		INSERT INTO #results SELECT 'Step 2 - ' + @fullname + ' ERROR:' + ERROR_MESSAGE()
	END CATCH

WORKS OKAY

		EXEC sp_changeobjectowner @fullname,'dbo'
		/* INSERT INTO #results SELECT 'Step 2 - ' + @fullname + ' ERROR:' + ERROR_MESSAGE() */

After running this, I can no longer see the tables in SSMS ... the server processor gets pegged at 95-99% ... until I kill the process.
Removing the "TRY" and "CATCH" makes this work beautifully, but I can't log the ERROR_MESSAGE(). :-(





The early bird gets the worm, but the second mouse gets the cheese.