Can you optimize a view with many UNION ALLs to external databases?
Using select * from vManyUnions where p = 1 will already avoid having to access the other tables, because of the contradiction optimisation.

If you look at the actual execution plan, you should see that only one of the external tables is actually accessed. Any others would have the NumberOfExecutions property set to zero.

You can see me demonstrate this in a talk that was recorded in 2009 for SQLBits, at http://bit.ly/Simplification