Hi
Think there's simple solution to this.
How do you get ALTER SCHEMA dbo TRANSFER @currentTable to use the
variable.

I'm doing an updated version of an SP to change the object Owner/Schema
and I don't want to use the now outdated sp_changeobjectowner

Cheers
Gary


----Old SP---
Create PROCEDURE [dbo].[ts_Maint_ChangeObjectOwners]

(
@currentOwner nVarchar(256),
@newOwner nVarchar(256)
)

AS
DECLARE @currentObject nvarchar(517)
DECLARE @qualifiedObject nvarchar(517)

DECLARE alterOwnerCursor CURSOR FOR

SELECT [name] FROM dbo.sysobjects
WHERE (xtype = 'U' or xtype = 'P' or xtype = 'FN' )
AND (LEFT([name], 2) <> 'dt' and Uid=user_id(@currentOwner))

OPEN alterOwnerCursor

FETCH NEXT FROM alterOwnerCursor INTO @currentObject

WHILE @@FETCH_STATUS = 0

BEGIN
SET @qualifiedObject = CAST(@currentOwner as varchar) + '.' +
CAST(@currentObject as varchar)
print 'Changing ' + @currentObject +' from '+ @currentOwner + ' to '
+ @newOwner
EXEC sp_changeobjectowner @qualifiedObject, @newOwner
FETCH NEXT FROM alterOwnerCursor INTO @currentObject
END

CLOSE alterOwnerCursor

DEALLOCATE alterOwnerCursor

RETURN

Pass the entire alter schema dbo...etc statement into an
exec().
EXEC('ALTER SCHEMA ' + @NewOwner + ' TRANSFER ' +
@QualifiedObject + ';')

-Sue

On 9 May 2006 16:53:34 -0700, "Gary" <gary (AT) internetalia (DOT) com>
wrote: