Altering multiple objects schema

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.

ALTER SP if exists in all databases

Hi,
I am trying to alter an SP in all my dB's. I am trying to loop through all
the databases ,however my script always returns false when it checks if the
SP exists (even though the SP exists)...for some reason it appears to be
still in master db even though I change db inside the cursor.
use master
declare @.AlterText = "ALTER PROCEDURE ..." --SP
declare @.dbName
declare c1 cursor for
select [name] from sysdatabases
open c1
fetch c1 into @.dbname
while(@.@.fetch_status = 0)
begin
exec('use ' + @.dbname)
if Exists(select name from sysobjects where name = 'sp_mysp' )
begin
print @.dbname
--exec (@.AlterText)
end
else
begin
print 'SP does not exist in '+@.dbname
end
fetch next from c1 into @.dbname
end
close c1
deallocate c1the scope of exec is only till the execution of that command.
"Mike" wrote:

> Hi,
> I am trying to alter an SP in all my dB's. I am trying to loop through all
> the databases ,however my script always returns false when it checks if th
e
> SP exists (even though the SP exists)...for some reason it appears to be
> still in master db even though I change db inside the cursor.
> use master
> declare @.AlterText = "ALTER PROCEDURE ..." --SP
> declare @.dbName
> declare c1 cursor for
> select [name] from sysdatabases
> open c1
> fetch c1 into @.dbname
> while(@.@.fetch_status = 0)
> begin
> exec('use ' + @.dbname)
> if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
> else
> begin
> print 'SP does not exist in '+@.dbname
> end
> fetch next from c1 into @.dbname
> end
> close c1
> deallocate c1|||Mike,
You only change the db for the exec() statement.
The current database after the exec() statement
is still master, since the USE result doesn't affect
the current database of the exec statement's caller.
A good way to do this kind of thing is to generate
all the statements you need to run by a single
query, then inspect the results and copy them by
hand and re-run them as a batch. For example,
here, you would run the output of
select
replace(replace('
use $db
goo
alter proc abc (
@.a int
) as
...
goo
','$db',quotename(name)),'goo','go')
from sysdatabases
The quotename() function protects against SQL injection
attacks from maliciously-named databases intended to cause
damage when scripts like yours are run.
You could select these strings with a cursor and execute them
also.
Be warned, however, that if your procedure does have the
name sp_something, you may run into surprises, because there
are some special name resolution rules for procedures whose
names begin with sp_. That prefix should not be used for
user-defined stored procedures.
Steve Kass
Drew Univeristy
Mike wrote:

>Hi,
>I am trying to alter an SP in all my dB's. I am trying to loop through all
>the databases ,however my script always returns false when it checks if the
>SP exists (even though the SP exists)...for some reason it appears to be
>still in master db even though I change db inside the cursor.
>use master
>declare @.AlterText = "ALTER PROCEDURE ..." --SP
>declare @.dbName
>declare c1 cursor for
>select [name] from sysdatabases
>open c1
>fetch c1 into @.dbname
>while(@.@.fetch_status = 0)
>begin
>exec('use ' + @.dbname)
>if Exists(select name from sysobjects where name = 'sp_mysp' )
> begin
> print @.dbname
> --exec (@.AlterText)
> end
>else
> begin
> print 'SP does not exist in '+@.dbname
> end
>fetch next from c1 into @.dbname
>end
>close c1
>deallocate c1
>|||By the way. Functionality you try to achieve with the script can be done by
using the following.
sp_msforeachdb 'use ? if Exists(select name from sysobjects where name =
''sp_mysp'' ) begin print ''?'' end'
Hope this helps.
--
"Omnibuzz" wrote:
> the scope of exec is only till the execution of that command.
> --
>
>
> "Mike" wrote:
>