Managing Databases


A database system instance is a single executing copy of a database system.

  • personal computers usually run one instance of a database system
  • clouds usually run multiple instances of a database system
  • each instance usually contains multiple system and user databases

Production database is a database available to end users for normal, day-to-day use.

Database SQL Commands

Create Database

CREATE DATABASE statement creates a new database.

  • To create a database:
CREATE DATABASE SampleScripts

Alter Database

ALTER DATABASE statement is used to change a database.

  • After creating a database, may need to change it using ALTER command
  • Example:
ALTER DATABASE [SampleScripts] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [SampleScripts] SET ANSI_NULLS OFF
GO

Delete Database

DROP DATABASE DatabaseName deletes a database, including all tables in the database.

Use Database

USE DatabaseName selects a default database for use in subsequent statements.

View Databases

The SHOW statement provides information about databases, tables, and columns:

  • SHOW DATABASES lists all databases in the database system instance
  • SHOW TABLES lists all tables in the default database
    • optional clause FROM DatabaseName lists tables in a named database
  • SHOW COLUMNS FROM TableName lists all columns in the TableName table of the default database
  • SHOW CREATE TABLE TableName shows the CREATE TABLE statement for the TableName table of the default database