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
- BISQL # 99 – ‘A to Z’ SQL Command # 1 – List of Command which are present in SQL Server 2008 R2 AND SQL Server 2012
- Starting of Brand New Series – ‘A to Z’ SQL Command Series
Today we are going to look at one of less used but highly important for every Database developer i.e. ALTER DATABASE
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
CONSTRAINT 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, UPDATEIn 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 
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.
- 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 :
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 |
-
July 16, 2012 at 10:49 pmBISQL # 103 : ‘A to Z’ SQL Command with Syntax, Description, Usage and Example # 2 : ALTER USER « SQL Server Mentalist
-
July 23, 2012 at 6:16 amBISQL # 105 : SQL Server 2012 : Resource Governor : Introduction, Syntax, Example, Resources and Explanation with SQL Server 2012 Enhancement « SQL Server Mentalist
SQL Server Mentalist 