none
Altering multiple objects schema

    Question

  • Hi,

    I need to change the schema of the stored procedures of several databases.

    Is there a way to put the alter schema statement within a loop that automaticaly processes all the stored procedures in a given database ?

    thank you

    Thursday, August 17, 2006 7:13 PM

Answers

  •   Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Friday, August 18, 2006 12:21 AM
    Moderator
  • You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

    declare @querystring nvarchar(MAX)

    set @querystring=''

    select @querystring=@querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

    exec(@querystring)

    Either way, you will have to use dynamic sql.

    Friday, August 18, 2006 3:26 PM

All replies

  •   Probably your best option is to use a cursor. You can find more information about them in BOL (http://msdn2.microsoft.com/en-us/library/ms180169.aspx)

     

      -Raul Garcia

      SDE/T

      SQL Server Engine

    Friday, August 18, 2006 12:21 AM
    Moderator
  • You can also try doing something like this. If NEWSCHEMA is the schema you want to transfer all the procedures to the following query should help

    declare @querystring nvarchar(MAX)

    set @querystring=''

    select @querystring=@querystring+' ALTER SCHEMA NEWSCHEMA TRANSFER ' + schema_name(schema_id) + '.' + name from sys.procedures

    exec(@querystring)

    Either way, you will have to use dynamic sql.

    Friday, August 18, 2006 3:26 PM