Use smo (SQL Server Management object) to generate a table creation script

I develop C # winform myself.

Recently came into contact with a task. Generate a script to create the table based on an existing table in SQL.

First, we want to solve the problem from the perspective of stored procedures.

I read some documents on the Internet and found that MSSQL itself does not have this function. I need to write it myself.

Other database objects (Views, stored procedures, and triggers) can be solved through sphelp_text.

After finding some information is fruitless, I decided to study it myself.

I read the SQL online documentation and found that there is an SMO development method.

To generate a script, the reference is

C: \ Program Files (x86) \ Microsoft SQL Server \ 100 \ SDK \ assemblies \ microsoft. sqlserver. connectioninfo. dll

C: \ Program Files (x86) \ Microsoft SQL Server \ 100 \ SDK \ assemblies \ microsoft. sqlserver. Management. SDK. SFC. dll

C: \ Program Files (x86) \ Microsoft SQL Server \ 100 \ SDK \ assemblies \ microsoft. sqlserver. SMO. dll

C: \ Program Files (x86) \ Microsoft SQL Server \ 100 \ SDK \ assemblies \ microsoft. sqlserver. smoextended. dll

C: \ Program Files (x86) \ Microsoft SQL Server \ 100 \ SDK \ assemblies \ microsoft. sqlserver. sqlenum. dll

 

Next we will introduce the source code of the script generation.

 

Code

 
Using System. Collections. Generic;
Using System. LINQ;
Using System. text;
Using Microsoft. sqlserver. Management. SMO;
Using Microsoft. sqlserver. Management. Common;
Using System. Collections. Specialized;

NamespaceGeyunfei
{
ClassProgram
{
Static VoidMain (String[] ARGs)
{

// Initialize a connection
Server =   New Server ( New Serverconnection (
" Localhost " , " SA " , "" ));
// Get Database
VaR srcdb = Server. Databases [ " Testdb " ];
// Get table
Table = Srcdb. Tables [ " Table1 " ];

 

// Initialize scripter
Scripter =   New scripter ();
. options. add (scriptoption. driallconstraints);
. options. add (scriptoption. driallkeys);
. options. add (scriptoption. default);
. options. add (scriptoption. continuescriptingonerror);
. options. add (scriptoption. convertuserdefineddatatypestobasetype);
. options. add (scriptoption. includeifnotexists);

Urncollection collection= NewUrncollection ();

Collection. Add (table. Urn );

VaR sqls=A. Script (Collection );

Foreach(VAR sInSqls)
{
System. Console. writeline (s );
}

}
}
}

 

 

 

Some useless words are provided:

How to find the SMO method...

1. The first response to the task is the stored procedure. I checked a bunch of data on the Internet and found thatProgramMs does not provide a method similar to sphelp_text. Although it can also be used, it is difficult to maintain most databases of specific versions.

2. Instead, use the response to generate scripts using SQL to LINQ files. However, it is still difficult to maintain the script for the last reason.

3. Remember that scripts can be generated in sqlserver mangement studio. So I flipped through the dynamic Connection Library of SSMs and tried several DLL names based on the name, which is too complicated and cannot be done.

4. I started to check the SQL online documentation and found this function. I tried it and it was really easy to use.