SQL Database Statement

SPS Home > Dgreath > RDBMS > SQL Database Statement

SQL DATABASE STATEMENT
The SQL "database" statement provides the means to define, modify, and remove database structures. Information about the databases can be obtained by query.

CREATE:
Creates a new database and the files used to store the database, or attaches a database from the files of a previously created database.

Syntax

General form:

CREATE DATABASE database_name [ ON [ <filespec> [ ,...n ] ] [ , < filegroup > [ ,...n ] ]] [ LOG ON { <filespec> [ ,...n ] } ] [ COLLATE collation_name ] [ FOR LOAD | FOR ATTACH ]

Typical < filespec > form:

[ PRIMARY ] ( [ NAME = logical_file_name , ] FILENAME = 'os_file_name' [ , SIZE = size ] [ , MAXSIZE = { max_size | UNLIMITED } ] [ , FILEGROWTH = growth_increment ] ) [ ,...n ]

Typical < filegroup > form:

FILEGROUP filegroup_name <filespec> [ ,...n ]

ALTER:
Adds or removes files and filegroups from a database. Can also be used to modify the attributes of files and filegroups, such as changing the name or size of a file. ALTER DATABASE provides the ability to change the database name, filegroup names, and the logical names of data files and log files. ALTER DATABASE supports setting various database options.

Syntax

ALTER DATABASE database_name


ADD FILE <filespec> [ ,...n ]
    
ADD FILE <filespec> [ ,...n ] [ TO FILEGROUP filegroup_name ]
    
ADD FILEGROUP filegroup_name
       
ADD LOG FILE <filespec> [ ,...n ]
       
COLLATE <collation_name>
    
MODIFY FILE <filespec>
      
MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name)
  
MODIFY FILEGROUP filegroup_name DEFAULT
       
MODIFY FILEGROUP filegroup_name NAME =new_filegroup_name
       
MODIFY FILEGROUP filegroup_name  READONLY
      
MODIFY FILEGROUP filegroup_name  READWRITE
      
MODIFY NAME = new_dbname
       
REMOVE FILE logical_file_name
       
REMOVE FILEGROUP filegroup_name
       
SET ANSI_NULL_DEFAULT OFF [ WITH <termination> ]
     
SET ANSI_NULL_DEFAULT ON [ WITH <termination> ]
     
SET ANSI_NULLS OFF [ WITH <termination> ]
     
SET ANSI_NULLS ON [ WITH <termination> ]
     
SET ANSI_PADDING OFF [ WITH <termination> ]
     
SET ANSI_PADDING ON [ WITH <termination> ]
     
SET ANSI_WARNINGS OFF [ WITH <termination> ]
     
SET ANSI_WARNINGS ON [ WITH <termination> ]
     
SET ARITHABORT OFF [ WITH <termination> ]
    
SET ARITHABORT ON [ WITH <termination> ]
    
SET AUTO_CLOSE ON [ WITH <termination> ]
    
SET AUTO_CLOSE OFF [ WITH <termination> ]
    
SET AUTO_CREATE_STATISTICS ON [ WITH <termination> ]
    
SET AUTO_CREATE_STATISTICS OFF [ WITH <termination> ]
    
SET AUTO_SHRINK OFF [ WITH <termination> ]
    
SET AUTO_SHRINK ON [ WITH <termination> ]
    
SET AUTO_UPDATE_STATISTICS OFF [ WITH <termination> ]
    
SET AUTO_UPDATE_STATISTICS ON [ WITH <termination> ]
    
SET CONCAT_NULL_YIELDS_NULL OFF [ WITH <termination> ]
    
SET CONCAT_NULL_YIELDS_NULL ON [ WITH <termination> ]
    
SET CURSOR_CLOSE_ON_COMMIT OFF [ WITH <termination> ]
    
SET CURSOR_CLOSE_ON_COMMIT ON [ WITH <termination> ]
    
SET CURSOR_DEFAULT GLOBAL [ WITH <termination> ]
    
SET CURSOR_DEFAULT LOCAL [ WITH <termination> ]
    
SET MULTI_USER [ WITH <termination> ]
      
SET NUMERIC_ROUNDABORT OFF [ WITH <termination> ]
        
SET NUMERIC_ROUNDABORT ON [ WITH <termination> ]
        
SET ONLINE [ WITH <termination> ]
      
SET OFFLINE [ WITH <termination> ]
      
SET QUOTED_IDENTIFIER OFF [ WITH <termination> ]
    
SET QUOTED_IDENTIFIER ON [ WITH <termination> ]
    
SET READ_ONLY [ WITH <termination> ]
      
SET READ_WRITE [ WITH <termination> ]
    
SET RECOVERY BULK_LOGGED [ WITH <termination> ]
       
SET RECOVERY FULL[ WITH <termination> ]
       
SET RECOVERY SIMPLE [ WITH <termination> ]
       
SET RECURSIVE_TRIGGERS OFF [ WITH <termination> ]
     
SET RECURSIVE_TRIGGERS ON [ WITH <termination> ]
     
SET RESTRICTED_USER [ WITH <termination> ]
      
SET SINGLE_USER [ WITH <termination> ]
      
SET TORN_PAGE_DETECTION OFF [ WITH <termination> ]
       
SET TORN_PAGE_DETECTION ON [ WITH <termination> ]

DROP:
Removes one or more databases from the server. Removing a database deletes the database and the disk files used by the database.

Syntax

DROP DATABASE database_name [ ,...n ]

LIST DATABASES:
List all databases located on a SQL Server in alphabetical order with DBID and size shown.

Syntax

USE master SELECT name, dbid, size FROM dbo.sysaltfiles WHERE fileID = 1 ORDER BY name

LIST A DATABASE'S CONFIGURATION:
List out the configuration of a database by DBID. Use the statement above to get the database's DBID.

Syntax

USE master SELECT * FROM dbo.sysaltfiles WHERE dbid = "db's ID number"

Arguments

ADD FILE <filespec>

Specifies that a file is added. The syntax is shown in CREATE DATABASE.

ADD FILE <filespec> TO FILEGROUP filegroup_name

Specifies a file to be added to the indicated filegroup.

ADD LOG FILE <log_file_name>

Specifies that a log file be added to the specified database.

ADD FILEGROUP <filegroup_name>

Specifies that a filegroup is to be added.

ANSI_NULL_DEFAULTOFF

CREATE TABLE does not follow SQL-92 rules to determine whether a column allows null values.

ANSI_NULL_DEFAULT ON

CREATE TABLE follows SQL-92 rules to determine whether a column allows null values.

ANSI_NULLS OFF

Comparisons of non-UNICODE values to a null value evaluate to TRUE if both values are NULL.

ANSI_NULLS ON

All comparisons to a null value evaluate to UNKNOWN.

ANSI_PADDING OFF

Strings are not padded to the same length before comparison or insert.

ANSI_PADDING ON

Strings are padded to the same length before comparison or insert.

ANSI_WARNINGS OFF

No errors or warnings are issued when conditions such as divide-by-zero occur.

ANSI_WARNINGS ON

Errors or warnings are issued when conditions such as divide-by-zero occur.

ARITHABORT OFF

A query will not terminate when an overflow or divide-by-zero error occurs during query execution.

ARITHABORT ON

A query is terminated when an overflow or divide-by-zero error occurs during query execution.

AUTO_CLOSE OFF

If OFF is specified, the database remains open after the last user exits.

AUTO_CLOSE ON

The database is shut down cleanly and its resources are freed after the last user exits.

AUTO_CREATE_STATISTICS OFF

Any missing statistics needed by a query for optimization are not built during optimization.

AUTO_CREATE_STATISTICS ON

Any missing statistics needed by a query for optimization are automatically built during optimization.

AUTO_SHRINK OFF

No automatic periodic shrinking will occur.

AUTO_SHRINK ON

Database files are candidates for automatic periodic shrinking.

AUTO_UPDATE_STATISTICS OFF

Any out-of-date statistics required by a query for optimization must be updated manually.

AUTO_UPDATE_STATISTICS ON

Any out-of-date statistics required by a query for optimization are automatically built during optimization.

COLLATE < collation_name >

Collation name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the default collation of the SQL Server instance.

collation_name

The default collation for the database. The default collation is latin1_general_CI_AS which is code page 1252, case insensitive, accent sensitive..

CONCAT_NULL_YIELDS_NULL OFF

The result of a concatenation operation is treated as an empty character string when either operand is NULL. The default is OFF.

CONCAT_NULL_YIELDS_NULL ON

The result of a concatenation operation is NULL when either operand is NULL.

CURSOR_CLOSE_ON_COMMIT OFF

Any cursors open remain open when a transaction is committed; rolling back a transaction closes any cursors except those defined as INSENSITIVE or STATIC.

CURSOR_CLOSE_ON_COMMIT ON

Any cursors open when a transaction is committed or rolled back are closed.

CURSOR_DEFAULT GLOBAL

Cursor scope defaults to GLOBAL.

CURSOR_DEFAULT LOCAL

Cursor scope defaults to LOCAL.

database_name

The name of the affected database. Database names must be unique within a server and conform to the rules for identifiers.

FILEGROUP

A logical grouping of files.

filegroup_name

The name of a filegroup. Must be unique and conform to the rules for identifiers.

FILEGROWTH

Specifies the growth increment of the file defined in the <filespec>. The FILEGROWTH setting for a file cannot exceed the MAXSIZE setting.

FILENAME

Specifies the operating-system file name for the file defined by the <filespec>.

FOR ATTACH

Specifies that a database is attached from an existing set of operating system files. There must be a <filespec> entry specifying the first primary file. The only other <filespec> entries needed are those for any files that have a different path from when the database was first created or last attached. A <filespec> entry must be specified for these files. The database attached must have been created using the same code page and sort order as SQL Server.

FOR LOAD

The database is created with the dbo use only database option turned on, and the status is set to loading.

growth_increment

The amount of space added to the file each time new space is needed expressed as an integer. A value of 0 indicates no growth. The value can be specified in MB, KB, GB, TB, or percent (%) with the default as MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. If FILEGROWTH is not specified, the default value is 10 percent and the minimum value is 64 KB. The size specified is rounded to the nearest 64 KB.

LOG ON

Specifies that the disk files used to store the database log (log files) are explicitly defined. The keyword is followed by a comma-separated list of <filespec> items defining the log files. If LOG ON is not specified, a single log file is automatically created with a system-generated name and a size that is 25 percent of the sum of the sizes of all the data files for the database.

logical_file_name

Is the name used to reference the file in any SQL statements executed after the database is created. logical_file_name must be unique in the database and conform to the rules for identifiers.

NAME

Specifies the logical name for the file defined by the <filespec>. The NAME parameter is not required when FOR ATTACH is specified.

MAXSIZE

Specifies the maximum size to which the file defined in the <filespec> can grow.

max_size

Is the maximum size to which the file defined in the <filespec> can grow. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. If max_size is not specified, the file grows until the disk is full.

MODIFY FILE

Specifies the given file that should be modified, including the FILENAME, SIZE, FILEGROWTH, and MAXSIZE options. Only one of these properties can be changed at a time. NAME must be specified in the <filespec> to identify the file to be modified. If SIZE is specified, the new size must be larger than the current file size. FILENAME can be specified only for files in the tempdb database, and the new name does not take effect until the server is restarted.

For optimum performance during multiple modify-file operations, several ALTER DATABASE database MODIFY FILE statements can be run concurrently.

MODIFY FILE (NAME = logical_file_name, NEWNAME = new_logical_name...)

To modify the logical name of a data file or log file, specify in NAME the logical file name to be renamed, and specify for NEWNAME the new logical name for the file.

For optimum performance during multiple modify-file operations, several ALTER DATABASE database MODIFY FILE statements can be run concurrently.

MODIFY FILEGROUP filegroup_name DEFAULT

Specifies the filegroup as the default database filegroup. Only one database filegroup can be default. CREATE DATABASE sets the primary filegroup as the initial default filegroup. New tables and indexes are created in the default filegroup—if no filegroup is specified in the CREATE TABLE, ALTER TABLE, or CREATE INDEX statements.

MODIFY FILEGROUP filegroup_name NAME =new_filegroup_name

Changes the filegroup name to the new_filegroup_name.

MODIFY FILEGROUP filegroup_name READONLY

Specifies the filegroup is read-only. Updates to objects in it are not allowed. The primary filegroup cannot be made read-only. Only users with exclusive database access can mark a filegroup read-only.

MODIFY FILEGROUP filegroup_name READWRITE

Reverses the READONLY property. Updates are enabled for the objects in the filegroup. Only users who have exclusive access to the database can mark a filegroup read/write.

MODIFY NAME = new_dbname

Renames the database.

MULTI_USER

MULTI_USER returns the database to its normal operating state after SINGLE_USER or RESTRICTED_USER operation.

n

A placeholder indicating that multiple files can be specified for the new database.

NUMERIC_ROUNDABORT OFF

No error is generated when loss of precision occurs in an expression.

NUMERIC_ROUNDABORT ON

An error is generated when loss of precision occurs in an expression.

ON

Specifies that the disk files used to store the data portions of the database (data files) are defined explicitly. The keyword is followed by a comma-separated list of <filespec> items defining the data files for the primary filegroup. The list of files in the primary filegroup can be followed by an optional, comma-separated list of <filegroup> items defining user filegroups and their files.

OFFLINE

Switches the database offline.

ONLINE

Switches the database online.

'os_file_name'

Is the path and file name used by the operating system when it creates the physical file defined by the <filespec>.

PRIMARY

Specifies that the associated <filespec> list defines the primary file. The primary filegroup contains all of the database system tables. It also contains all objects not assigned to user filegroups. The first <filespec> entry in the primary filegroup becomes the primary file, which is the file containing the logical start of the database and its system tables. A database can have only one primary file. If PRIMARY is not specified, the first file listed in the CREATE DATABASE statement becomes the primary file.

QUOTED_IDENTIFIER OFF

Double quotation marks can not be used to enclose delimited identifiers. This is the default.

QUOTED_IDENTIFIER ON

Double quotation marks can be used to enclose delimited identifiers.

READ_ONLY

Specifies whether the database is in read-only mode where users can only read data from the database, not modify it. The database cannot be in use when READ_ONLY is specified. The master database is the exception, and only the system administrator can use master while READ_ONLY is set.

READ_WRITE

Returns the database to normal read/write operations.

RECOVERY

The default recovery model is determined by the recovery model of the model database. To change the default for new databases, use ALTER DATABASE to set the recovery option of the model database.

RECOVERY BULK_LOGGED

Provides protection against media failure combined with the best performance and least amount of log memory usage for certain large scale or bulk operations. These operations include SELECT INTO, bulk load operations (either bcp or BULK INSERT), CREATE INDEX, and text and image operations (WRITETEXT and UPDATETEXT).

Under the bulk-logged recovery model, logging for the entire class is minimal and cannot be controlled on an operation-by-operation basis.

RECOVERY FULL

Complete protection against media failure is provided. If a data file is damaged, media recovery can restore all committed transactions.

RECOVERY SIMPLE

A simple backup strategy that uses minimal log space is provided. Log space can be automatically reused when no longer needed for server failure recovery.

RECURSIVE_TRIGGERS OFF

Prevents direct recursion only. This is the default.

To disable indirect recursion as well, set the nested triggers server option to 0 using sp_configure.

RECURSIVE_TRIGGERS ON

Recursive firing of triggers is allowed.

REMOVE FILE

Removes the file description from the database system tables and deletes the physical file. The file cannot be removed unless empty.

REMOVE FILEGROUP

Removes the filegroup from the database and deletes all the files in the filegroup. The filegroup cannot be removed unless empty.

RESTRICTED_USER

When specified, only members of the db_owner, dbcreator, or sysadmin roles can use the database.

SINGLE_USER

When specified, only one user at a time can access the database.

SIZE

Specifies the size of the file defined in the <filespec>.

size

Is the initial size of the file defined in the <filespec>. The kilobyte (KB), megabyte (MB), gigabyte (GB), or terabyte (TB) suffixes can be used. The default is MB. Specify a whole number; do not include a decimal. The minimum value for size is 512 KB.

TORN_PAGE_DETECTION OFF

Incomplete pages can not be detected.

TORN_PAGE_DETECTION ON

Incomplete pages can be detected. This is the default.

UNLIMITED

Specifies that the file defined in the <filespec> grows until the disk is full.

WITH <termination>

Specifies when to roll back incomplete transactions when the database is transitioned from one state to another. Only one of the following termination clauses can be specified and it follows the SET clauses. If the termination clause is omitted, transactions are allowed to commit or roll back on their own.

ROLLBACK AFTER integer [SECONDS]: Specifies whether to roll back after the specified number of seconds.
ROLLBACK IMMEDIATE: Specifies whether to roll back immediately.
NO_WAIT: Specifies that if the requested database state or option change cannot complete immediately without waiting for transactions to commit or roll back on their own, the request will fail.

Syntax

Syntax

Syntax

Syntax

Syntax

Arguments