We have a number of stored procedures that need to run against a set of similar databases (~25). Our desire is a single proc that can step through all the databases, probably via a cursor.
Unfortunately, we run into issues because the USE statement does not allow a variable. The only solution we've come up with is to put the entire proc into a variable, inserting the target db into it, and using sp_sqlexec. Many of the procs are 100+ lines long, and the process of changing single-quotes to double-quotes and staying under the 4096 nvarchar limit for sp_sqlexec is time-consuming and tedious. And troubleshooting the query in the variable is a pain.
Ideal:
SET @dbn = 'MyDB' (or take @dbn from a cursor of database names)
USE @dbn
Only way I've found:
SET @cmd = 'USE ' + @dbn
+ ' ...rest of the script code which could be hundreds of lines...'
EXEC sp_sqlexec @cmd
I must be missing something, because it seems like this shouldn't be so hard. Does anyone know a better/easier way?
Thanks
Unfortunately, we run into issues because the USE statement does not allow a variable. The only solution we've come up with is to put the entire proc into a variable, inserting the target db into it, and using sp_sqlexec. Many of the procs are 100+ lines long, and the process of changing single-quotes to double-quotes and staying under the 4096 nvarchar limit for sp_sqlexec is time-consuming and tedious. And troubleshooting the query in the variable is a pain.
Ideal:
SET @dbn = 'MyDB' (or take @dbn from a cursor of database names)
USE @dbn
Only way I've found:
SET @cmd = 'USE ' + @dbn
+ ' ...rest of the script code which could be hundreds of lines...'
EXEC sp_sqlexec @cmd
I must be missing something, because it seems like this shouldn't be so hard. Does anyone know a better/easier way?
Thanks