Click here to Skip to main content
13,300,654 members (53,905 online)
Click here to Skip to main content
Add your own
alternative version


57 bookmarked
Posted 22 Feb 2008

Create a SQL Database Programmatically

, 24 Oct 2008
Rate this:
Please Sign up or sign in to vote.
In this article, I will explain how to use Microsoft SQL Management Objects (SMO) to create a Microsoft SQL database from C# code.


In this article, I will explain how to use Microsoft SQL Management Objects (SMO) to create a Microsoft SQL database from C# code. In this sample, I will:

  1. Enumerate all the SQL servers in the network and fill a list box where the user can select one of the servers.
  2. The user will enter the Database Name to create.
  3. The user will enter an AD account to grant right to.
  4. When the user clicks "Create Database", the system will check for the existence of the database.


SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. You can use SMO to build customized SQL Server management applications. Although SQL Server Management Studio is a powerful and extensive application for managing SQL Server, there might be times when you would be better served by an SMO application.

Enumerating SQL Servers

The SmoApplication.EnumAvailableSqlServers() enumerates a list of available instances of Microsoft SQL Server. It returns a DataTable object value that contains an enumerated list of information about the available instances of SQL Server. The table describes the different columns of the returned DataTable.

Column Data type Description
Name String The name of the instance of SQL Server.
Server String The name of the server on which the instance of SQL Server is installed.
Instance String The instance of SQL Server.
IsClustered Boolean A Boolean value that is true if the instance is participating in failover clustering, or false if it is not.
Version String The version of the instance of SQL Server.
IsLocal Boolean A Boolean value that is true if the instance is local, or false if the instance is remote.
DataTable dt = SmoApplication.EnumAvailableSqlServers();
foreach (DataRow dr in dt.Rows)

Connecting to SSQL Server

First we check if the user selected the local server or a SQL server:

//Connect to the local, default instance of SQL Server.
string srvname = this.cbServers.SelectedItem as string;
Server srv;
if (srvname == null)
    srv = new Server();
    sb.AppendLine("Connected to local SQL server");
    srv = new Server(srvname);
    sb.AppendLine(string.Format("Connected to {0}", srvname));

Checking If the Database Exists

It is easy to check if the database already exists or not, just use the Databases attribute of the server as follows:

//Define a Database object variable by supplying the server and the 
//database name arguments in the constructor. 
Database db = srv.Databases[this.tbDBName.Text.Trim()];
if (db != null)
    if (MessageBox.Show(string.Format
	("The '{0}' already exists do you want to drop it?", this.tbDBName.Text), 
	"Warning", MessageBoxButtons.YesNo, 
	MessageBoxIcon.Question) == DialogResult.Yes)else 
        if (MessageBox.Show(string.Format
	("Create the Tables and Stored Procedures for BT Error Manager on '{0}'?", 
	this.tbDBName.Text), "Warning", MessageBoxButtons.YesNo, 
	MessageBoxIcon.Question) == DialogResult.Yes)
            b.AppendLine("Creating the Tables and Stored Procedures.");
            this.tbProgress.Text = sb.ToString();
	   ("Created the Tables and Stored Procedures for BT Error Manager on '{0}'", 
	   this.tbDBName.Text));this.tbProgress.Text = sb.ToString();
	   this.tbProgress.ScrollToCaret();"Proceed or select another database");
	   this.tbProgress.Text = sb.ToString();this.tbProgress.ScrollToCaret();

Creating the Database

To create a database, all you have to do is create a new Database object with the Server and the name of the database, then call the Create method.

db = new Database(srv, this.tbDBName.Text);
this.tbProgress.Text = sb.ToString();
//Create the database on the instance of SQL Server. 
sb.AppendLine("Created the database.");
sb.AppendLine("Creating the Tables and Stored Procedures.");
this.tbProgress.Text = sb.ToString();this.tbProgress.ScrollToCaret(); 

Running the SQL Script

The last step is to run the script that will create the tables, views, stored procedures, etc.

//'Reference the database and display the date when it was created. 


If you want to use this code in your project, you will need to add references to the SMO assemblies. You can locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:

  • Microsoft.SqlServer.ConnectionInfo.dll
  • Microsoft.SqlServer.Smo.dll
  • Microsoft.SqlServer.SqlEnum.dll
  • Microsoft.SqlServer.SmoEnum.dll


Visit to find more information.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Architect Genetic Thought Software Inc.
Canada Canada
Over 20 years of experience developing software solutions, leading the Architecture, Design, and Refactoring of many large projects. At FCT led the architecture for several project in parallel, Motion (Releases 7, 8 and 9), Integrating with 3rd party providers (PPSA/Chattel Project) and the identity management initiative among others. At Bell/ Sympatico MSN web modernization project led the BizTalk development team, delivered the solution architecture, developed the core framework, mentored and supported the team members on the Solution and BizTalk technical issues. At Siemens led the architecture, design and implementation of the Real time server and Newscaster for the HiPath Pro (A CRM System). Holds IEEE/CSDP, MCSD (6.0, and .Net) and Oracle certifications. Act as a referee for the IEEE Software magazine. With International and US experience in Financial, Insurance, Retail, Marketing and Telecommunications services automation.

You may also be interested in...


Comments and Discussions

GeneralMy vote of 4 Pin
Mohammed Hameed31-May-13 4:57
professionalMohammed Hameed31-May-13 4:57 
QuestionMissing an assembly ? Get error message ! Please help. Pin
gautom bose30-Aug-12 15:20
membergautom bose30-Aug-12 15:20 
QuestionCreating Database Propgramitically Pin
solomon20110-Mar-12 10:55
membersolomon20110-Mar-12 10:55 
I am trying to create a database and it's tables programmatically. I am following the Instructions from the link below but i keep getting this error ExecuteNonQuery failure. Please can someone look at the code? especially the table generation part because the Database creates but there are no tables in it.

Create a SQL Database Programmatically[^]
GeneralCreating database in another place Pin
persiaprog4-Jul-10 7:24
memberpersiaprog4-Jul-10 7:24 
GeneralRun the existing SQL Script programmatically with SMO Pin
elizas25-Mar-10 3:46
groupelizas25-Mar-10 3:46 
Generalwell..few comments Pin
Md. Marufuzzaman25-Aug-09 7:03
groupMd. Marufuzzaman25-Aug-09 7:03 
GeneralRe: well..few comments Pin
Md. Marufuzzaman25-Aug-09 7:06
groupMd. Marufuzzaman25-Aug-09 7:06 
Generalthanks for info Pin
yianfang31-Jan-09 4:56
memberyianfang31-Jan-09 4:56 
QuestionAnother one? Pin
Mark Nischalke22-Feb-08 8:42
memberMark Nischalke22-Feb-08 8:42 
AnswerRe: Another one? Pin
Moustafar25-Feb-08 6:47
memberMoustafar25-Feb-08 6:47 
GeneralRe: Another one? Pin
Mark Nischalke25-Feb-08 7:37
memberMark Nischalke25-Feb-08 7:37 
JokeRe: Another one? Pin
myklk21-Apr-08 5:18
membermyklk21-Apr-08 5:18 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.171207.1 | Last Updated 24 Oct 2008
Article Copyright 2008 by Moustafar
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid