Complex View creation question

I need to create a cross-database view (same server) in a master database.
The databases I'm joining in the view are listed in a table in that master.
I'd like for the view to automatically include new databases whose names are
added to that table in the master. How would I go about doing this? I'm no
t
familiar with dynamic sql... TIA.Views aren't going to have dynamic SQL in them, and I wouldn't really
recommend a multi-line table user-defined function even though you could
probably accomplish what you want by using dynamic SQL in it.
I would recommend a hook in your application/middle-tier (optimally) or a
trigger on that master table (less optimal) that would ALTER the view to
include all the existing databases in your master table whenever the list of
databases changes. Just create the ALTER VIEW statement based on the list
from the master table. If done from the app, you have many ways to do this;
if from a trigger, you'd create the SQL string via a cursor or a funky
SELECT statement and then execute it via EXEC (dynamic SQL).
The things to consider in this scenario would be: what impact does changing
the view have on the live system? how frequently does this change? does
the user adding/deleting records in the master table have permissions to
alter the view?
Mike
"William Sullivan" <WilliamSullivan@.discussions.microsoft.com> wrote in
message news:AD0E4C8D-1983-4003-B8FE-09D0222548F4@.microsoft.com...
>I need to create a cross-database view (same server) in a master database.
> The databases I'm joining in the view are listed in a table in that
> master.
> I'd like for the view to automatically include new databases whose names
> are
> added to that table in the master. How would I go about doing this? I'm
> not
> familiar with dynamic sql... TIA.