使用 vb.net 将表编写为 CREATE TO

问题描述

在 SQL Server 中，我可以创建一个表，该表是另一个表的副本，其中设置了所有约束.我可以在 SQL server management studio 中使用脚本表作为 CREATE TO 来做到这一点.然后我可以在另一个数据库中运行脚本，以便重新创建同一个表但没有数据.我想通过使用 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.

推荐答案

您可以使用 SMO(SQL Server 管理对象)程序集将表编写为应用程序内的字符串.我在这里使用 C#，但同样可以在 VB.NET 中轻松完成.

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();

您需要引用多个 SMO 程序集.

You need to reference several SMO assemblies.

在此处阅读有关 SMO 及其使用方法的更多信息:

Read more about SMO and how to use it here:

• SQL Server 入门管理对象 (SMO)
• 生成使用 SMO for SQL Server 的数据库对象脚本

这篇关于使用 vb.net 将表编写为 CREATE TO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！