IT researcher IT researcher - 8 days ago 7
SQL Question

Script table as CREATE TO by using vb.net

In SQL server I can create a table which is duplicate of another table with all constraints set in it. I can use script table as CREATE TO in SQL server management studio to do this. Then I can run the script in another database so that same table is recreated but without data. I want to do same by using vb.net code. Important point is that all the constraints and table properties are set properly.

Answer

You can use the SMO (SQL Server Management Objects) assembly to script out tables to a string inside your application. I'm using C# here, but the same can be done easily in VB.NET, too.

// Define your database and table you want to script out
string dbName = "YourDatabase";
string tableName = "YourTable";

// set up the SMO server objects - I'm using "integrated security" here for simplicity
Server srv = new Server();
srv.ConnectionContext.LoginSecure = true;
srv.ConnectionContext.ServerInstance = "YourSQLServerInstance";

// get the database in question
Database db = new Database();
db = srv.Databases[dbName];

StringBuilder sb = new StringBuilder();

// define the scripting options - what options to include or not
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;

// script out the table's creation 
Table tbl = db.Tables[tableName];

StringCollection coll = tbl.Script(options);

foreach (string str in coll)
{
    sb.Append(str);
    sb.Append(Environment.NewLine);
}

// you can get the string that makes up the CREATE script here
// do with this CREATE script whatever you like!
string createScript = sb.ToString();

You need to reference several SMO assemblies.

Read more about SMO and how to use it here: