SQL Server Create Database Query

How to create database in SQL Server ?

'create database' statement is used to create a database in SQL Server.

Create Database Syntax

CREATE DATABASE <DATABASE NAME>;

Create Database Example

Below SQL is used to create a database 'learn_db' in SQL Server.

CREATE DATABASE learn_db;

Output:

Command(s) completed successfully.

Do we need any privilege to create database ?

We need admin privilege to create a database in SQL Server, Check the privileges enable for logged in user before creating any databases.

How to list the databases ?

This SELECT query is used to list the available databases in the SQL Server.

SELECT name FROM master.dbo.sysdatabases

Output: We can also execute the stored procedure to get the list of available databases.

EXEC sp_databases

Output:

How to get only user defined databases ?

List only user defined databases if we filter with dbid is greater than 4.

SELECT [name] 
FROM master.dbo.sysdatabases 
WHERE dbid > 4 

We can also add condition like below to filter system databases.

select name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');

Output:

How to drop the database in SQL server ?

DROP DATABASE statement is used to drop the database.

DROP DATABASE <DATABASE NAME>;

DROP DATABASE Example

DROP DATABASE learn_db1

Output:

Command(s) completed successfully.

When you drop the database which is also active database, then you will get below error.

Cannot drop the database 'learn_db', because it does not exist or you do not have permission.

Change the active database as master or other than this database to drop it.
« Previous Next »