CODEWITHSUNDEEP
sql-server
tonychunga
tonychunga

Reputation: 13

Use Cases of sys.databases and master.dbo.sysdatabases

I'm trying to learn mssql with Rocky Linux on RHEL 8, however I've found examples of people pulling database names using

SELECT name FROM master.dbo.sysdatabases

I don't understand how this command was created, I'm not finding anything that points to using master.dbo.

I used this to obtain the same results, in my mind this is more straight forward and makes more sense. Maybe I'm missing something crucial - but that's why I'm asking a question.

SELECT name FROM sys.databases

Maybe in my use-case it doesn't make a difference, but is there a preferred method between these options? I feel like I'm pulling hairs, but I'm just curious.

Upvotes: 1

Views: 1343

Answers (1)

anon
anon

Reputation:

master is the name of the primary database, it is where many DMVs and catalog views are sourced, though several of those are also accessible from other databases (and even when referenced from other schemas).

master.dbo.sysdatabases

^ Specifically, this is a 3-part name, in the form [database].[schema].[object]. But it references a backward compatibility view that is only still there to avoid breaking code written before SQL Server 2005. You can see plenty of warnings about this here:

Note that it works with both dbo.sysdatabases and sys.sysdatabases because the schema part of the name is essentially ignored.

Your instinct is right that the catalog view is the better way to query the list of databases, both because it isn't deprecated and because it includes a much more complete set of properties about a database:

SELECT * FROM sys.databases;

But you could also say:

SELECT * FROM mydatabase.sys.databases;
SELECT * FROM master.sys.databases;

But there are other catalog views where this is not true. For example, these will give very different, database-specific results:

SELECT * FROM mydatabase.sys.tables;
SELECT * FROM master.sys.tables;

Some other background:

Upvotes: 2

Related Questions