I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database will always have the table
member which will always have the columns
dob. The stored procedure
find_new_members() would use exactly the same SQL no matter which database it was executed against.
I've found the command
CREATE SYNONYM which looks like it will do the trick if I do something like this:
DECLARE @database NVARCHAR(16) = 'ClientA'; DECLARE @statement NVARCHAR(MAX) = 'CREATE SYNONYM CurrentDB FOR ' + @database; EXECUTE sp_executesql @statement; // Run generic statements
This allows me to farm the generic SQL out to a stored proc, and then just pass in the target database name. The problem is, it looks like the SYNONYM is scoped at a database level, so this means I can't have an SSIS package that runs the scripts against each client in their own stream. Not without the SYNONYM changing and affecting all scripts.
Is it possible to scope a SYNONYM statement to a user-level or (ideally) a PID-level, so that I can at least kick off multiple jobs at once, each using a different value for the SYNONYM
EDIT: To explain my use case a little more, we are running queries from a database we have full read-write-execute on, against multiple linked databases we only have read rights on. Those databases are snapshots of source systems that we have no control over, and are simply databases sitting in a data lake. If we start throwing around `sp_executesql statements on the master scripts, we're going to lose a lot of refactoring and code control aspects in our IDEs. Ideally, we're looking at "aliasing" a linked database at a PID level.