Generate sql script using c# code

class Program
{
static void Main(string[] args)
{
string dbConnectionString = “Server=./sqlexpress;Database=mydata;User ID=sa;Password=test@111;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;”;
string databaseName = “mydata”;

ScriptDatabase(dbConnectionString, databaseName);
}

public static string ScriptDatabase(string dbConnectionString, string databaseName)
{

SqlConnection conn = new SqlConnection(dbConnectionString);
ServerConnection serverConn = new ServerConnection(conn);
var server = new Server(serverConn);
var database = server.Databases[databaseName];

var scripter = new Scripter(server);
/* With ScriptingOptions you can specify different scripting
* options, for example to include IF NOT EXISTS, DROP
* statements, output location etc*/
ScriptingOptions scriptOptions = new ScriptingOptions();
scriptOptions.ScriptDrops = true;
scriptOptions.ScriptData = true;
scriptOptions.ScriptSchema = true;
scriptOptions.IncludeIfNotExists = true;
string scrs = “”;
string tbScr = “”;
foreach (Table myTable in database.Tables)
{
/* Generating IF EXISTS and DROP command for tables */
StringCollection tableScripts = myTable.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + “\n\n”;

/* Generating CREATE TABLE command */
tableScripts = myTable.Script();
foreach (string script in tableScripts)
tbScr += script + “\n\n”;
}
foreach (StoredProcedure mySP in database.StoredProcedures)
{
/* Generating IF EXISTS and DROP command for StoredProcedures */
StringCollection tableScripts = mySP.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + “\n\n”;

/* Generating CREATE StoredProcedure command */
tableScripts = mySP.Script(scriptOptions);
foreach (string script in tableScripts)
tbScr += script + “\n\n”;
}

foreach (View myView in database.Views)
{
/* Generating IF EXISTS and DROP command for Views */
StringCollection tableScripts = myView.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + “\n\n”;

/* Generating CREATE Views command */
tableScripts = myView.Script(scriptOptions);
foreach (string script in tableScripts)
tbScr += script + “\n\n”;
}
foreach (Microsoft.SqlServer.Management.Smo.User user in database.Users)
{
/* Generating IF EXISTS and DROP command for Users */
StringCollection tableScripts = user.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + “\n\n”;

/* Generating CREATE Users command */
tableScripts = user.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + “\n\n”;
}

foreach (Microsoft.SqlServer.Management.Smo.UserDefinedFunction userF in database.UserDefinedFunctions)
{
/* Generating IF EXISTS and DROP command for UserDefinedFunctions */
StringCollection tableScripts = userF.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + “\n\n”;

/* Generating CREATE UserDefinedFunction command */
tableScripts = userF.Script(scriptOptions);
foreach (string script in tableScripts)
scrs += script + “\n\n”;
}

// For WinForms
return (scrs + “\n\n” + tbScr);
//For Console
//Console.WriteLine(scrs + “\n\n” + tbScr);
}
}

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