Why do I have to query the full path in ssms?

Question

I want to query in ssms but I always have to add the specific schema as a prefix, although I have ran the query:

USE (the specific db I wanna use);   
GO 

What should I do for ssms to bring back only tables from the specific db and schemas while querying?

Answer 1

Within SQL Server, you use the Fully Qualified Name. That consists of three parts (though technically, when using a linked server, you could add a servername part as well):

• Database
• Schema
• Table

And can be used in the following manner:

SELECT * FROM <database>.<schema>.<table>

The USE keyword simply changes the context in which you are executing a SQL command. It's identical to using the drop-down box in SSMS to change to a different database.

By switching the database context, you can typically skip the part of the query above. By switching context, it is assumed all commands will be executed within the database you changed to.

The reason it's still there is if you want to access objects that physically reside within a different database on the same SQL Server instance.

The schema is just a way to group your tables. The default schema is database owner (dbo). If you omit the schema name, it's assumed the object is in the dbo schema. So the following 2 commands are assumed to be identical:

SELECT * FROM dbo.MyTable
SELECT * FROM MyTable

However, using schemas is a great way to structure your database, as you can logically group related objects within the same schema, and assign permissions accordingly.

From an OLTP perspective, you could have a schema dealing with orders, and one with sales. That way it is easier for people to filter only the objects they are interested in, and for the dba to limit access to schemas to specific departments.

If you work with data warehousing, it's not unusual to see an Extract schema, a Stage schema, and a Fact and Dimension schema.