How can I generate CREATE TABLE script from code?

Question

In SQL Server Management Studio, I can generate the CREATE TABLE script for a table by right-clicking a table and choosing Script Table As.

How can I get this same result in C#? Can I utilize SMO or some other methodology?

[To avoid the question getting closed, please post working code samples, not one-liners or links to the high-level documentation.]

Answer 1

Table Script

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

Answer 2

You can try below functions to get table script from SQL Server Database using C#. Complete Article: Transfer data or script between two SQL Server databases

C# Code:

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

Answer 3

Here's a slightly more complete example (stolen from my buddy 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();
        }
    }
}

Answer 4

The following code will create a script at location "QQQ" by specifying the server "XXX", table "ZZZ" and schema "PPP". There are a few example scripts floating out there to do copies of entire databases, this is for just tables. This is what I was trying to figure out this whole time and I finally got it working using the code below. This is meant to be a simple example, the resulting scripts for instance don't create the indexes of a table, just its most basic structure. To specify how the script is created, pass an instance of ScriptingOptions into the call to 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();

        }
    }
}