Your sample query
Select Empid, Deptname From Employee E INNER JOIN Department D ON (E.Dept_id=D.Dept_id) Will this work??-----
Insted if i write
Select Empid, Deptname From AdventureWork .Employee E INNER JOIN AdventureWorkDW .Department D ON (E.Dept_id=D.Dept_id)--- This is working fine.
But our requiremnt is we dont want to mention the schema name. Is tis possible??
This is not describing a schema, AdventureWorkDW, and AdventureWork are database names.
If you want to eliminate the database name from your query then you must be connected to that database.
If your purposly connected to AdventureWorks, and you need data from the AdventureWorkDW database then you need to fully qualify the AdventureWorksDW table in your query by using the [DatabaseName].[Schema].[Table Name] in your Join.
Like I mentioned I'm confused by what your asking because you seem to be calling database names Schemas, and they are different. and the examples you posted do not work as you mention.