Thursday, 23 April 2009

Bulk schema change for stored procs in SQL Server

When you need to change the schema for SPs.

Remember this:

DECLARE
  @OldOwner sysname,
  @NewOwner sysname
 SET @OldOwner = 'oldOwner'
 SET @NewOwner = 'dbo'
DECLARE CURS CURSOR FOR
SELECT
  name
FROM
 sysobjects
WHERE
  type = 'p' 
AND
  uid = (SELECT uid FROM sysusers WHERE name = @OldOwner) 
AND 
  NOT name LIKE 'dt%' FOR READ ONLY
DECLARE @ProcName sysname
OPEN CURS
FETCH CURS INTO @ProcName
WHILE @@FETCH_STATUS = 0
BEGIN
    IF @@VERSION >= 'Microsoft SQL Server 2005'
    BEGIN
        EXEC('alter schema ' + @NewOwner + ' transfer ' + @OldOwner + '.' + @ProcName)
        exec('alter authorization on ' + @NewOwner + '.' + @ProcName + ' to schema owner')
    END
    ELSE
        EXEC('sp_changeobjectowner ''' + @OldOwner + '.' + @ProcName + ''', ''' + @NewOwner + '''')
    FETCH CURS INTO @ProcName
END
CLOSE CURS
DEALLOCATE CURS


Courtesy of Tim Page; http://www.geekzilla.co.uk/ViewC1D86C71-13E7-477B-94EC-FD01B524B6C3.htm 



k