CODEWITHSUNDEEP
sql-server
punam Gat
punam Gat

Reputation: 11

How to copy all schema from one database to other database (created at runtime)?

I have used schema for tables for one database,so how to copy all schema from one database to other database(database created at runtime)

string sql = "create database " + str1;

SqlCommand command = new SqlCommand(sql, connection);
connection.Open();
command.ExecuteNonQuery();
Response.Write("database created");
connection.Close();



string sqll = "(select * into " + str1 + ".cost_category.cost_category_info 
              from ERPAccounting.cost_category.cost_category_info where 1=2)
              (select * into " + str1 + ".dbo.cost_centre_info from 
              ERPAccounting.cost_centre.cost_centre_info where 1=2)"

connection.Open();
SqlDataAdapter ad = new SqlDataAdapter(sqll, connection);
DataSet ds = new DataSet();
ad.Fill(ds);

Upvotes: 1

Views: 274

Answers (1)

userfl89
userfl89

Reputation: 4810

Using C#, object DDL can be obtained by using SMO objects and then executed in the database where the objects need to be copied to. In the example below, references to Microsoft.SqlServer.Management.Smo, Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc, and System.Data.SqlClient are necessary. The DDL is first obtained from the SMO objects, then it used as the CommandText for the SqlCommand that is executed in the destination database. This example is for tables, but other objects (views, stored procedures, etc.) can also be copied using this method.

            //set initial catalog to destination database
            string connStr = @"Data Source=YourServer;Initial Catalog=DestinationDatabase;Integrated Security=SSPI;";

            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //set source server and database using SMO objects
                Server srv = new Server(@"YourServer");
                srv.ConnectionContext.LoginSecure = true;
                srv.ConnectionContext.StatementTimeout = 600;
                Database db = srv.Databases["SourceDatabase"];

                //configure Scripter for DDL
                Scripter script = new Scripter(srv);
                ScriptingOptions scriptOpt = new ScriptingOptions();

                //SQL command to execute DDL in destination database
                SqlCommand sql = new SqlCommand();
                sql.Connection = conn;
                conn.Open();

                //this can changed to views, SPs, etc. as needed
                foreach (Table t in db.Tables)
                {
                    //check for system objects
                    if (!t.IsSystemObject)
                    {
                        StringCollection sc = t.Script(scriptOpt);
                        foreach (string s in sc)
                        {
                            //assign and execute DDL
                            sql.CommandText = s;
                            sql.ExecuteNonQuery();
                        }
                    }
                }
            }

Upvotes: 1

Related Questions