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
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
|||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.
No comments:
Post a Comment