SQL CREATE DATABASE statement is used to create a new database in SQL server.
Database in SQL Server is a collection of tables that stores a specific set of structured data. You can easily interact with data such as you can Select, Update and Delete the data in tables.
Syntax
CREATE DATABASE Datbase_name ;
Lets create a new database in SQL Server using Create database statement.
As you can see the list of available database in SQL Server.
CREATE DATABASE using T-SQL
Now we will create a new database named as DEV_DB in SQL Server.
Following create database statement creates a new database- DEV_DB in SQL Server .
CREATE DATABASE DEV_DB ;
Open Query editor window, and execute the above query.
Once you execute the query, you will see a commands completed successfully as shown in below screen.
Now just go to object explorer and refresh the databases folder.
Once you refresh the folder, you can see the database DEV_DB that you created.
You can also check the list of available databases in SQL Server using following T-SQL statement.
SELECT NAME, DATABASE_ID, CREATE_DATE FROM MASTER.dbo.SYSDATABASES;
You can see, database that you created is also there in lists.
If you want to check for specific database existence in SQL Server, then you can modify above query as following.
SELECT NAME, DATABASE_ID, CREATE_DATE FROM SYS.DATABASES WHERE NAME = N'DEV_DB';
Now you can see, it return database only if it exists in SQL Server and this Database exists in SQL Server.
DELETE Database in SQL Server
You can delete database from SQL Server using DROP command.
Go to Query Editor window and write following Query, and execute the query.
DROP DATABASE DEV_DB;
Once you execute the DROP Database Query, now go to object explorer and refresh the databases folder and you will see that database removed from SQL Sever as shown in below screenshot.
You can verify same executing following statement, it returns blank data that means now database DEV_DB does not exists in SQL Server.
SELECT NAME, DATABASE_ID, CREATE_DATE FROM SYS.DATABASES WHERE NAME = N'DEV_DB';
Creating a DATABASE using SQL Server Management Studio
Open SQL Server Management Studio, now go to the object explorer, then Right click on the Databases folder then click on New database.. option in the context menu shown in below screenshot.
Once you click on New Databases.., a New Database dialog box opens.
Provide name for database, here we are leaving owner as default.
Next click on OK button.
Once you click on OK button, you can check the databases folder in object explorer a database DEV_DB is created successfully.
RENAME DATABASE in SQL Server
You can also rename an existing database, Lets rename an existing database DEV_DB with DEV_LIVE.
To rename database in SQL server, you can use the system stored procedure sp_renamedb.
Syntax for renaming the database as follows.
SP_RENAMEDB [Old Database Name],[New Database Name]
Following statement rename the database – DEV_DB with DEV_live
SP_RENAMEDB 'DEV_DB', 'DEV_LIVE' ;
Lets execute the statement, and you can see a message which says ‘The database name ‘DEV_LIVE’ has been set’, that means database name has changed successfully.
Now go to object explorer and refresh the Databases folder, you can see now the database name DEV_DB is changed with DEV_LIVE.
Also Read..
