SQL equivalent of "using" for schemas?

Question

I'm working with a SQL Server DB that's got tables spread across multiple schemas (not my idea), so queries end up looking like this:

select col1, col2
from some_ridiculously_long_schema_name.table1 t1
inner join
    another_really_long_schema_location.table2 t2
    on...

... you get the idea.

This is a small inconvenience when I put queries into stored procs, etc., but when I'm doing adhoc queries, this gets to be a real pain.

Is there some way I could "include" all the schemas I'm interesed in, and have them automatically addressable? (LINQPad does this).

I'd love to be able to be able to indicate something like this:

using some_ridiculously_long_schema_name, another_really_long_schema_location

... and then query away, with those schemas included in my address space.

If nothing like this exists, I'll look into synonymns, but I'd prefer to do this without having to add artifacts into the DB.

Answer 1

no it doesn't. synonims are the only way.

Answer 2

That will not work because if you have Table1 in both schemas then how would you know what schema you want?

Answer 3

Red-Gate sells an SQL tool that adds intellisense to server management studio. Never tried it but it might help cut down on the keystrokes: http://www.red-gate.com/products/SQL_Prompt/index.htm

Answer 4

I know how you feel, if you need to keep the schemas (for example if you have the same table names in each) and you are consistently writing queries that join across the schemas the best suggestion I can offer is to shorten your schema names.

Low tech and not what you wanted to hear I am sure.

Synonyms as suggested above only work at an object level (you cant have a synonym for a whole schema as far as I know) so you would have to have a synonym for every table, view, stored proc, function etc that you wanted to use from outside your default schema.