SQL Server System Databases

In the previous post we have learnt about SSMS (SQL Server Management Studio) and how to connect with a SQL Server instance. In this post we will learn about different system databases which are available in SQL Server.

Different System Databases

SQL Server contains the following system databases.

1. master database
2. model database
3. msdn database
4. tempdb database

Press F8 to open the Object Explorer window if it is not open. Click the System Databases folder. You get the available system databases as shown below in the snapshot.

Now we understand each system database in brief one by one.

Master Database: The master database contains the details of all the database objects created in different databases. This contains system tables, views etc. Click the root node of master database. We get folders like Tables, Views, Synonyms, Programmability, Storage, and Security. Expand the Tables node, you get the following

1. System Tables folder
2. External Tables folder
3. Graph Tables folder

Other system databases have similar folders in hierarchical order.

Now you will learn about some important concepts like system catalog which is frequently used to query different user defined database objects. You will mainly use master database in most of the cases. Click the master database node to expand it. Next, click the Views folder and then System Views folder. The System Views contain a huge number of system objects.

Among them, a few important ones are sys.objects, sys.schema, sys.tables, sys.views, sys.triggers etc. Query the sys.objects to get details of different objects like user tables, system tables, user views etc.

SYS.OBJECTS Example: Press CTRL+N to open the query editor window and paste the following code:

SELECT * FROM SYS.OBJECTS

Note that system databases can be queried from any database. Press F5 to run the code. You get the following result.

Now focus on the Type column of the result set. You can use the Type to query specific type of objects e.g. user table, views, internal table, system table etc. For example to get the details of all user tables, you run the following query 

SELECT * FROM SYS.OBJECTS WHERE TYPE = 'U'.

Similarly, you run the following query to get the details of all views.

SELECT * FROM SYS.OBJECTS WHERE TYPE = 'V'

In the next post, you will learn about how to create database in a SQL Server.