Home > A to Z SQL Command, Link, SQL > BISQL # 101 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 1 : ALTER DATABASE

BISQL # 101 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 1 : ALTER DATABASE

Hi Folks ,

Lets start after removing some unwanted command from our ‘A to Z’ SQL Command Series.

Pervious links of this series are as follows

Our list for ‘A to Z’ SQL Command Series is as follows :-

    ALTER DATABASE (This Post), ALTER GROUP, ALTER TABLE, ALTER TRIGGER, ALTER USER, BEGIN, CHECKPOINT, CLOSE, COMMIT, CREATE AGGREGATE, CREATE CAST, CREATE image_thumb1CONSTRAINT TRIGGER, CREATE CONVERSION, CREATE DATABASE, CREATE DOMAIN, CREATE FUNCTION, CREATE GROUP, CREATE INDEX, CREATE LANGUAGE, CREATE OPERATOR, CREATE OPERATOR CLASS, CREATE RULE, CREATE SCHEMA, CREATE SEQUENCE, CREATE TABLE, CREATE TABLE AS, CREATE TRIGGER, CREATE TYPE, CREATE USER, CREATE VIEW, DEALLOCATE, DECLARE, DELETE, DROP AGGREGATE, DROP CAST, DROP CONVERSION, DROP DATABASE, DROP DOMAIN, DROP FUNCTION, DROP GROUP, DROP INDEX, DROP LANGUAGE, DROP OPERATOR, DROP OPERATOR CLASS, DROP RULE, DROP SCHEMA, DROP SEQUENCE, DROP TABLE, DROP TRIGGER, DROP TYPE, DROP USER, DROP VIEW, END, EXECUTE, FETCH, GRANT, INSERT, LOAD, MOVE, RESET, REVOKE, ROLLBACK, SELECT, SELECT INTO, SET, SET CONSTRAINTS, SET SESSION AUTHORIZATION, SET TRANSACTION, START TRANSACTION, TRUNCATE, UPDATE

In this article we are going to cover

  • Introduction to ALTER DATABASE Command
  • Syntax for ALTER DATABASE Command
  • Description of ALTER DATABASE Command
  • Use of ALTER DATABASE Command
  • Example of ALTER DATABASE Command

Introduction to ALTER DATABASE Command image

Modifies a database, or the files and filegroups associated with the database. Adds or removes files and filegroups from a database, changes the attributes of a database or its files and filegroups, changes the database collation, and sets database options. Database snapshots cannot be modified

Syntax for ALTER DATABASE Command

ALTER DATABASE { database_name  | CURRENT }
{
    MODIFY NAME = new_database_name
  | COLLATE collation_name
  | <file_and_filegroup_options>
  | <set_database_options>
}
[;]

<file_and_filegroup_options >::= 
  <add_or_modify_files>::=
  <filespec>::=
  <add_or_modify_filegroups>::=
  <filegroup_updatability_option>::=

<set_database_options>::=
  <optionspec>::=
  <auto_option> ::=
  <change_tracking_option> ::=
  <cursor_option> ::=
  <database_mirroring_option> ::=
  <date_correlation_optimization_option> ::=
  <db_encryption_option> ::=
  <db_state_option> ::=
  <db_update_option> ::=
  <db_user_access_option> ::=
  <external_access_option> ::=
  <FILESTREAM_options> ::=
  <HADR_options> ::= 
  <parameterization_option> ::=
  <recovery_option> ::=
  <service_broker_option> ::=
  <snapshot_option> ::=
  <sql_option> ::=
  <termination> ::=

       

Description of ALTER DATABASE Command

Where all fields above can be explained as :

  • database_name :Is the name of the database to be modified.
  • CURRENT :Designates that the current database in use should be altered.
  • CONTAINMENT:Specifies the containment status of the database. OFF = non-contained database. PARTIAL = partially contained database.
  • MODIFY NAME =new_database_name :Renames the database with the name specified as new_database_name.
  • COLLATE collation_name : Specifies the collation for the database. collation_name can be either a Windows collation name or a SQL collation name. If not specified, the database is assigned the collation of the instance of SQL Server.
 
Use of ALTER DATABASE Command

  • ALTER DATABASE : Provides the syntax for changing the name and the collation
  • ALTER DATABASE File and Filegroup Options : Provides the syntax for adding and removing files and filegroups from a database, and for changing the attributes of the files and filegroups.
  • ALTER DATABASE SET Options : Provides the syntax for changing the attributes of a database by using the SET options of ALTER DATABASE.
  • ALTER DATABASE Database Mirroring : Provides the syntax for the SET options of ALTER DATABASE that are related to database mirroring.
  • ALTER DATABASE SET HADR : Provides the syntax for the AlwaysOn Availability Groups options of ALTER DATABASE for configuring a secondary database on a secondary replica of an AlwaysOn availability group.
  • ALTER DATABASE Compatibility Level : Provides the syntax for the SET options of ALTER DATABASE that are related to database compatibility levels.
    Example of ALTER DATABASE Command

Changing the name of a database

The following example changes the name of the AdventureWorks2012 database to AdventureWorks

USE master;
GO
ALTER DATABASE AdventureWorks2012
Modify Name = AdventureWorks ;
GO

Changing the collation of a Database

USE master;
GO

CREATE DATABASE testdb
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

ALTER DATABASE testDB
COLLATE French_CI_AI ;
GO

Hope this helps and you got how to use ALTER DATABSE Command  !!

Those who have not ye subscribe my Blog yet they can subscribe it !So that I can post you @ real time and all sort of knowledge in your mail without Zero spamming !!

Happy Learning and Sharing !!

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

Link Resource Blog >> Daily Interesting links

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Connect With me on

| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |

Advertisements
Categories: A to Z SQL Command, Link, SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: