在SQL Server Management Studio中，我可以通过右键单击表并选择“Script Table As”生成表的CREATE TABLE脚本。
我应该如何在C#中获得同样的结果？我可以利用SMO或其他方法吗？
[为了避免问题被关闭，请发布可用的代码示例，而不是一行代码或高级文档的链接。]
我应该如何在C#中获得同样的结果？我可以利用SMO或其他方法吗？
[为了避免问题被关闭，请发布可用的代码示例，而不是一行代码或高级文档的链接。]
如何从代码生成CREATE TABLE脚本？
11
- sooprise
2
Microsoft.SqlServer.Management.Smo.Table.Script()。示例：http://msdn.microsoft.com/en-us/library/ms162153%28v=sql.105%29 - Sergei Rogovtcev
如果您想备份表和数据，为什么要将输出作为脚本文件呢？使用COPY_ONLY备份不是更加方便吗？@sooprise - Aaron Bertrand
4个回答
10
以下代码将通过指定服务器“XXX”，表“ZZZ”和模式“PPP”的位置，在“QQQ”位置创建一个脚本。目前有一些示例脚本可用于整个数据库的复制，但这只是针对表格的。这就是我一直在尝试解决的问题，最终我使用下面的代码使其工作。这只是一个简单的示例，例如生成的脚本不会创建表的索引，仅包含其最基本的结构。要指定如何创建脚本，请将ScriptingOptions的实例传递给table.Script()调用。
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.SqlEnum;
using System.Configuration;
using System.Collections.Specialized;
namespace SmoTest {
class Program {
static void Main(string[] args) {
Server server = new Server("XXX");
Database database = new Database();
database = server.Databases["YYY"];
Table table = database.Tables["ZZZ", @"PPP"];
StringCollection result = table.Script();
var script = "";
foreach (var line in result) {
script += line;
}
System.IO.StreamWriter fs = System.IO.File.CreateText(@"QQQ");
fs.Write(script);
fs.Close();
}
}
}
- sooprise
6
这里有一个稍微更完整的例子（从我的朋友Ben Miller那里借来的）：
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.SqlEnum;
using Microsoft.SqlServer.Management.Smo.CoreEnum;
using System.Configuration;
using System.Collections.Specialized;
namespace SmoTest
{
class Program
{
static void Main(string[] args)
{
Server srv = new Server();
// really you would get these from config or elsewhere:
srv.ConnectionContext.Login = "foo";
srv.ConnectionContext.Password = "bar";
srv.ConnectionContext.ServerInstance = "ServerName";
string dbName = "DatabaseName";
Database db = new Database();
db = srv.Databases[dbName];
StringBuilder sb = new StringBuilder();
foreach(Table tbl in db.Tables)
{
ScriptingOptions options = new ScriptingOptions();
options.ClusteredIndexes = true;
options.Default = true;
options.DriAll = true;
options.Indexes = true;
options.IncludeHeaders = true;
StringCollection coll = tbl.Script(options);
foreach (string str in coll)
{
sb.Append(str);
sb.Append(Environment.NewLine);
}
}
System.IO.StreamWriter fs = System.IO.File.CreateText("c:\\temp\\output.txt");
fs.Write(sb.ToString());
fs.Close();
}
}
}
- Aaron Bertrand
2
你对 ScriptingOptions 类有何深入的了解？例如，我该如何确保获得与现有表完全相同的副本（包括所有属性，如索引或其他）？这里有太多要设置的属性，我不知道哪些要设置，哪些要保持原样。 - sooprise
0
您可以尝试以下函数使用C#从SQL Server数据库获取表脚本。完整文章：在两个SQL Server数据库之间传输数据或脚本
C#代码：
public string GetTableScript(string TableName, string ConnectionString) { string Script = "";string Sql = "declare @table varchar(100)" + Environment.NewLine + "set @table = '" + TableName + "' " + Environment.NewLine + //"-- set table name here" + "declare @sql table(s varchar(1000), id int identity)" + Environment.NewLine + " " + Environment.NewLine + //"-- create statement" + "insert into @sql(s) values ('create table [' + @table + '] (')" + Environment.NewLine + " " + Environment.NewLine + //"-- column list" + "insert into @sql(s)" + Environment.NewLine + "select " + Environment.NewLine + " ' ['+column_name+'] ' + " + Environment.NewLine + " data_type + coalesce('('+cast(character_maximum_length as varchar)+')','') + ' ' +" + Environment.NewLine + " case when exists ( " + Environment.NewLine + " select id from syscolumns" + Environment.NewLine + " where object_name(id)=@table" + Environment.NewLine + " and name=column_name" + Environment.NewLine + " and columnproperty(id,name,'IsIdentity') = 1 " + Environment.NewLine + " ) then" + Environment.NewLine + " 'IDENTITY(' + " + Environment.NewLine + " cast(ident_seed(@table) as varchar) + ',' + " + Environment.NewLine + " cast(ident_incr(@table) as varchar) + ')'" + Environment.NewLine + " else ''" + Environment.NewLine + " end + ' ' +" + Environment.NewLine + " ( case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' + " + Environment.NewLine + " coalesce('DEFAULT '+COLUMN_DEFAULT,'') + ','" + Environment.NewLine + " " + Environment.NewLine + " from information_schema.columns where table_name = @table" + Environment.NewLine + " order by ordinal_position" + Environment.NewLine + " " + Environment.NewLine + //"-- primary key" + "declare @pkname varchar(100)" + Environment.NewLine + "select @pkname = constraint_name from information_schema.table_constraints" + Environment.NewLine + "where table_name = @table and constraint_type='PRIMARY KEY'" + Environment.NewLine + " " + Environment.NewLine + "if ( @pkname is not null ) begin" + Environment.NewLine + " insert into @sql(s) values(' PRIMARY KEY (')" + Environment.NewLine + " insert into @sql(s)" + Environment.NewLine + " select ' ['+COLUMN_NAME+'],' from information_schema.key_column_usage" + Environment.NewLine + " where constraint_name = @pkname" + Environment.NewLine + " order by ordinal_position" + Environment.NewLine + //" -- remove trailing comma" + " update @sql set s=left(s,len(s)-1) where id=@@identity" + Environment.NewLine + " insert into @sql(s) values (' )')" + Environment.NewLine + "end" + Environment.NewLine + "else begin" + Environment.NewLine + //" -- remove trailing comma" + " update @sql set s=left(s,len(s)-1) where id=@@identity" + Environment.NewLine + "end" + Environment.NewLine + " " + Environment.NewLine + "-- closing bracket" + Environment.NewLine + "insert into @sql(s) values( ')' )" + Environment.NewLine + " " + Environment.NewLine + //"-- result!" + "select s from @sql order by id";
DataTable dt = GetTableData(Sql, ConnectionString); foreach (DataRow row in dt.Rows) { Script += row[0].ToString() + Environment.NewLine; }
return Script; }
public DataTable GetTableData(string Sql, string ConnectionString) { SqlConnection con = new SqlConnection(ConnectionString); try { con.Open(); SqlCommand selectCommand = new SqlCommand(Sql, con); DataSet dataSet = new DataSet(); new SqlDataAdapter(selectCommand).Fill(dataSet); DataTable table = dataSet.Tables[0]; return table; } catch (Exception) { return new DataTable(); } finally { con.Close(); } }
- csharpbd
0
表格脚本
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Management;
using System.Data.SqlClient;
namespace GenrateScriptsForDatabase
{
class Program
{
static void Main(string[] args)
{
var server = new Server(new ServerConnection { ConnectionString = new SqlConnectionStringBuilder { DataSource = @"Your Server Name", UserID="Your User Id",Password="Your Password" }.ToString() });
server.ConnectionContext.Connect();
var database = server.Databases["Your Database Name"];
using (FileStream fs = new FileStream(@"H:\database_scripts\Gaurav.sql", FileMode.Append, FileAccess.Write))
using (StreamWriter sw = new StreamWriter(fs))
{
for each (Table table in database.Tables)
{
if (table.Name == "Your Table Name")
{
var scripter = new Scripter(server) { Options = { ScriptData = true } };
var script = scripter.EnumScript(new SqlSmoObject[] { table });
for each (string line in script)
{
sw.WriteLine(line);
Console.WriteLine(line);
}
}
}
}
}
}
}
- Gaurav Chauhan
2
命名空间是：
using System;
using System.Collections.Generic;
using System.Linq;
System.Text;
using System.Threading.Tasks;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Common;
using System.Collections.Specialized;
using System.IO;
using System.Data;
using Microsoft.SqlServer.Management;
using System.Data.SqlClient; - Gaurav Chauhan
网页内容由stack overflow 提供, 点击上面的可以查看英文原文，
原文链接
原文链接
- 相关问题
- 3 为一系列索引生成CREATE脚本
- 6 从SELECT语句生成CREATE TABLE脚本
- 22 在BigQuery中的“Create Table As”
- 3 何时使用CREATE TABLE AS SELECT，何时使用CREATE TABLE LIKE？
- 3 自动生成 SQL Server CREATE ASSEMBLY 脚本
- 10 H2DB - 在CREATE TABLE请求中使用的SQL ON UPDATE脚本
- 5 MySQL CREATE TABLE行为异常
- 3 如何正确解析SQL CREATE TABLE语句
- 6 Java：从现有表生成CREATE TABLE代码
- 9 使用管理工具生成CREATE TABLE脚本