Tag Archives: Sql System Databases

How to get User, System and All Databases name in Sql Server

Sql Server, , , , , , , , , , , , , ,

How to get all Databases name in Sql Server

We can write a query like below to get the list of all databases on an instance of an Sql Server. This list includes both the system databases as well as the user created databases.

SELECT name FROM SYS.DATABASES

RESULT:
get-all-databases-in-sql-server

How to get only the User Created Databases name in Sql Server

If you want to get only the User Created databases name from an instance of an Sql Server, then we can write a query like below. Here, I am filtering the know System Databases from the result. As far as I know Sql Server doesn’t provide any mechanism with which we can identify a database as a system database or user created database.

SELECT name AS 'User Created Databases'
FROM SYS.DATABASES
WHERE name NOT IN 
  ('master', 'model', 'msdb', 'tempdb', 'Resource',
       'distribution' , 'reportserver', 'reportservertempdb')

RESULT:
get-all-user-databases-in-sql-server-1

How to get only the System Databases name in Sql Server

If you want to get only the system databases name from an instance of an Sql Server, then we can write a query like below.

SELECT name
FROM SYS.DATABASES
WHERE name IN ('master', 'model', 'msdb', 'tempdb', 'resource',
       'distribution' , 'reportserver', 'reportservertempdb')

RESULT:
get-all-system-databases-in-sql-server-1

The number of System Databases returned varies from one instance of Sql Server to another instance of the Sql Server. For example if you have installed the Sql Server reporting services then only the instance of Sql Server will have the reportserver and reportservertempdb databases.

[ALSO READ] 100 Frequently used queries in Sql Server

Working with Databases in Sql Server

Sql Server, , , ,

Sql Server Tutorial Lesson 2: Working with Databases

DATABASE

A Database in Sql Server consists of mainly database objects like Tables, Stored Procedures, User Defined Functions, Views and so on. Let us first understand how to Create Database, once we create the Database we can add other database objects to it which we learn over the course of this Tutorial.

CREATE DATABASE

Below is the Basic Syntax for Creating a Database in Sql Server

CREATE DATABASE DataBaseName

Demo 1: Let us Create a DataBase with Name SqlHintsTutorial

CREATE DATABASE SqlHintsTutorial

USE DATABASE

A database instance will usually be having multiple System Defined databases and User created databases. We can use the USE statement to select the Database on which we want to perform the database operations. Basically USE statement changes the database context to the Specified Database. Below is the basic syntax of the USE statement.

USE DatabaseName

Demo 2:Let us make the SqlHintsTutorial database as the current contextual database.

USE SqlHintsTutorial

DROP DATABASE

We can drop a database using DROP Statement. Dropping a database will permanently removes all the information stored in it, so be careful before using it. Below is the basic syntax for Dropping the Database.

DROP DATABASE DatabaseName

We can’t drop the database which is the current contextual database. To do it we have to change the current contextual database to some-other database and then issue the DROP statement as shown below.

DEMO 3: Let us try to drop a database which is the current Contextual database

USE SqlHintsTutorial
GO
DROP DATABASE SqlHintsTutorial

RESULT:
Msg 3702, Level 16, State 3, Line 1
Cannot drop database “SqlHintsTutorial” because it is currently in use.

DEMO 4: Below script demonstrate how to delete the current contextual database by changing the Current Context DB to some other database and then drop it

 -- Change Current db Context to System Database TempDB
USE TempDB
GO
DROP DATABASE SqlHintsTutorial

System Databases

Below are the list of System Databases which shipped with Sql Server by default. These databases are required for the Sql Server to function smoothly.

System Database Description
MASTER Records all system-level information for an instance of SQL Server.
MSDB Used by SQL Server Agent for scheduling alerts and jobs.
MODEL Used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.
TEMPDB It is used for holding temporary objects or intermediate result-sets.