Copy tables and SP from MS SQL DB to another

Is there anyway I can copy tables and stored procedures from one MS SQL server database to another? I presume there is, so how is it done.

Thanks

Are you looking for something like theSQL Server Hosting Toolkit?|||Yes this will help me when uploading to the server but i am speaking about combining databases locally.|||

You can use the SMO classes to generate scripts for all you tables and stored procedures. Add a reference to Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo. You can use the SQLBulkCopy class to copy data between tables.

Imports Microsoft.SqlServer.Management.Smo
Imports System.Text

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim srv As New Server(".\sqlexpress")
Dim sb As New StringBuilder

Dim db As Database = srv.Databases("NorthWind")
For Each tbl As Table In db.Tables
sb.Append(tbl.Name & "<br/><br/>")
Dim sc As Specialized.StringCollection = tbl.Script()
For Each s As String In sc
sb.Append(s & "<br/>")
Next
sb.Append("<br/><br/>")
Next

For Each q As StoredProcedure In db.StoredProcedures
sb.Append(q.Name & "<br/><br/>")
Try
Dim sq As Specialized.StringCollection = q.Script
For Each s As String In sq
sb.Append(s & "<br/>")
Next
Catch
End Try
sb.Append("<br/><br/>")
Next
Response.Write(sb.ToString)
End If
End Sub
End Class

|||

Ken Tucker:

You can use the SMO classes to generate scripts for all you tables and stored procedures. Add a reference to Microsoft.SqlServer.ConnectionInfo and Microsoft.SqlServer.Smo. You can use the SQLBulkCopy class to copy data between tables.

Imports Microsoft.SqlServer.Management.Smo
Imports System.Text

Partial Class _Default
Inherits System.Web.UI.Page

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If Not Me.IsPostBack Then
Dim srv As New Server(".\sqlexpress")
Dim sb As New StringBuilder

Dim db As Database = srv.Databases("NorthWind")
For Each tbl As Table In db.Tables
sb.Append(tbl.Name & "<br/><br/>")
Dim sc As Specialized.StringCollection = tbl.Script()
For Each s As String In sc
sb.Append(s & "<br/>")
Next
sb.Append("<br/><br/>")
Next

For Each q As StoredProcedure In db.StoredProcedures
sb.Append(q.Name & "<br/><br/>")
Try
Dim sq As Specialized.StringCollection = q.Script
For Each s As String In sq
sb.Append(s & "<br/>")
Next
Catch
End Try
sb.Append("<br/><br/>")
Next
Response.Write(sb.ToString)
End If
End Sub
End Class

Thanks for this. Any chance of it in C#? Like to try it out.

|||

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.SqlServer.Management.Smo;
using System.Text;
using System.Collections.Specialized;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
if (this.IsPostBack != true)
{
StringBuilder sb = new StringBuilder();
Server svr = new Server(".\\sqlexpress");
Database db = svr.Databases["NorthWind"];
sb.Append("Tables<br/><br/>");
foreach (Microsoft.SqlServer.Management.Smo.Table tbl in db.Tables)
{
if (tbl.IsSystemObject != true)
{
sb.Append(tbl.Name + "<br/><br/>");
StringCollection sc = tbl.Script();
foreach (string s in sc)
{
sb.Append(s + "<br/>");
}
sb.Append("<br/><br/>");
}
}
sb.Append("Queries<br/><br/>");
foreach (StoredProcedure q in db.StoredProcedures)
{
if (q.IsSystemObject != true)
{
sb.Append(q.Name + "<br/>");
StringCollection scQuery = q.Script();
foreach (string sq in scQuery)
{
sb.Append(sq + "<br/>");
}
sb.Append("<br/><br/>");
}
}

Response.Write(sb.ToString());
}

}
}

|||Thanks for all your help.