How am I able to query master tables/view without pass database name?

Question

Just for curiosity, I'd like to know how I am able to select some system table/view stored in master database without pass the DB NAME prefix.

ie, this works fine for master_files table from any database:

use pombo;
go
select * from sys.master_files

I have created a table in master database and I need to pass the db_name (master), otherwise it does not work from other database.

ie:

select * from dbo.some_table;
Msg 208, Level 16, State 1, Line 1
Invalid object name 'some_table'.

Is this configurable? I mean, can I configure a table to be selected from any database without pass the DB NAME? or this behavior only apply to those system tables like master_files?

Answer 1

While there's no way to create a "global" table (without creating synonyms in each database), there is with stored procedures.

When you create a procedure with the sp_ prefix in Master it's callable in the context of any database. But the static SQL in the procedure resolves object names in the context of Master database, rather than the context of the database containing the stored procedure. So you can use a procedure called sp_some_table to return data in master.dbo.some_table from any database.

If you want to reference objects in the local database use dynamic SQL in the stored procedure, as object names in dynamic SQL resolve relative to the current database and the current user's default schema.

Answer 2

Is this configurable? I mean, can I configure a table to be selected from any database without pass the DB NAME?

No, you have to specify the database context one way or another, either with:

1. The 3-part naming convention (e.g. SELECT * FROM master.dbo.some_table)
2. The USE keyword to set the context to that database (e.g. USE master; SELECT * FROM dbo.some_table;)
3. Specifying the database name master as part of your connection string in whichever app you use to connect to the database, so the default context is master. (SSMS has a Default Database setting you configure for each Login object.)

or this behavior only apply to those system tables like master_files?

sys.master_files is a special system-wide view (as mentioned in the docs), which is why it's accessible in any database. When you call it from the pombo database, you're using the instance of that view that lives in the pombo database. I'm not positive how Microsoft architects it, but I'd assume it's a view in the model database, as objects that live in model get copied to new user created databases.

I have created a table in master database

Don't do this. It's not recommended to change the system databases. Whatever you goal is, there's probably a better alternative solution.

Answer 3

Whether you can do this or not depends on:

1. If the view should access a table in the master database or if it is expected to access a table in the current database.
2. If you are OK with using unsupported functionality.

If the answer to 2 is "no", then you are out of luck, as J.D. and David has written.

If the answer to 2 is "yes" and answer to 1 is that you want to access object in the current database, then again you are out of luck.

If the answer to 2 is "yes" and answer to 1 is that you want to access a table in the master database, then read on:

A lesser known feature in MSSQL is that you can in the master database create a view named sp_something. This will make it global, but it will access the table in the master database, as shown below:

USE master
GO
DROP TABLE IF EXISTS myTable
GO
CREATE TABLE myTable(c1 int)
INSERT INTO myTable(c1) VALUES(42)
GO
CREATE OR ALTER VIEW sp_myView
AS
SELECT c1 FROM myTable
GO
CREATE OR ALTER VIEW sp_myview3
AS
SELECT name from sys.tables WHERE name like 'Sales%'
GO
--Below doesn't change behavior
--EXEC sp_MS_marksystemobject 'sp_myview3'
GO
USE Adventureworks
GO
DROP TABLE IF EXISTS myTable
GO
CREATE TABLE myTable(c1 int)
INSERT INTO myTable(c1) VALUES(99)
GO
--CREATE OR ALTER VIEW sp_myView
--AS
--SELECT c1 FROM myTable
--GO
--CREATE OR ALTER VIEW sp_myview3
--AS
--SELECT name from sys.tables WHERE name like 'Sales%'
--GO
--Returns 42
SELECT * FROM sp_myView
--Doesn't return any of the "sales" tables
SELECT * FROM sp_myView2