Create a User Database

You can create a new database if a system administrator has granted you permission to use create database. You must be using the master database when you create a new database.

In many enterprises, a system administrator creates all databases. The creator of a database is its owner. Another user who creates a database for you can use sp_changedbowner to transfer ownership of it.

The database owner is responsible for giving users access to the database and for granting and revoking certain other permissions to users. In some organizations, the database owner is also responsible for maintaining regular backups of the database and for reloading it in case of system failure. The database owner can use the setuser command to temporarily attain any other user’s permissions on a database.

Because each database is allocated a significant amount of space, even if it contains only small amounts of data, you may not have permission to use create database.

The simplest form of create database is:

create database database_name

To create a new database called newpubs database, verify you are using the master database rather than pubs2, then enter:

use master
create database newpubs 
drop database newpubs
use pubs2

A database name must be unique on SAP ASE, and must follow the rules for identifiers. SAP ASE can manage up to 32,767 databases. You can create only one database at a time. The maximum number of segments (a label that points to one or more database devices) for any database is 32.

SAP ASE creates a new database as a copy of the model database, which contains the system tables that belong in every user database.

The creation of a new database is recorded in the master database tables sysdatabases and sysusages.

See the Reference Manual: Commands and the Reference Manual: Tables.

The with overide option allows machines with limited space to maintain their logs on device fragments that are separate from their data. This is not recommend, but for machines with limited storage, it may be an option. For information about with override, see, Creating and Managing User Databases, the System Administration Guide: Volume 2.

• The on Clause
  Use the on clause to specify where to store a database and how much space, in megabytes, to allocate for the database.
• The log on Clause
  Unless you are creating very small, noncritical databases, always use the log on clause with the create database command. Using this extension places the transaction logs on a separate database device.
• for load Option
  The optional for load clause invokes a streamlined version of create database that you can use only for loading a database dump.