Monday, March 29, 2010

CREATE DATABASE (T-SQL)

Create a SQL Server Database using T-SQL

Your need to create a database by using T-SQL. The simplest command you can issue to achieve this is:
CREATE DATABASE <database_name>
If you open the database, you will notice that its metadata has been set to some values and perhaps, the database already contains tables, stored procedures, UDFs and other objects. How does this magic work? Actually, the SQL Server derives a new database from the model database. Below is how MSDN explains this process:
All user-defined objects in the model database are copied to all newly created databases. You can add any objects, such as tables, views, stored procedures, data types, and so on, to the model database to be included in all newly created databases.
When a CREATE DATABASE database_name statement is specified without additional size parameters, the primary data file is made the same size as the primary file in the model database.
Unless FOR ATTACH is specified, each new database inherits the database option settings from the model database. For example, the database option auto shrink is set to true in model and in any new databases you create. If you change the options in the model database, these new option settings are used in any new databases you create. Changing operations in the model database does not affect existing databases. If FOR ATTACH is specified on the CREATE DATABASE statement, the new database inherits the database option settings of the original database.
I think the same is true when you create a database by using ADO.NET or SMO.
So, if you want that all databases SQL Server creates for you be initialized with certain parameters and/or contain some objects, tweak your Model DB.

CREATE DATABASE (ADO.NET and C#)

Create a SQL Server Database Programmatically

You need to create a SQL Server database programmatically by using ADO.NET and C#. Here is a quick recipe how to do that.
using System;
using System.Data.SqlClient;

SqlConnection localConn = new SqlConnection ("(local); Integrated security=SSPI; database=master");
string SQLCommand = "CREATE DATABASE TestDb";

SqlCommand DbCreate = new SqlCommand(SQLCommand, localConn);
try
{
  localConn.Open();
  DbCreate.ExecuteNonQuery();
  Console.WriteLine("DataBase is Created Successfully");
}
catch (System.Exception ex)
{
  Console.WriteLine(ex.ToString());
}
finally
{
  if (localConn.State == ConnectionState.Open)
  {
    myConn.Close();
  }
}
This code creates a database similar to the SQL Server Model database and places its files to the default location. If you want to create a database with custom properties, please check out MSDN.