Looking for a SQL Server tool

[Todd Smith]

I need a tool that will script out the views, functions and stored procedures for a SQL Server DB into one file per object with DROP/CREATE. SSMS w/SP2 has that in the wizard now but its too cumbersome. I checked out Scriptio[^] but it will need some hacking before it's gtg. Red-gate can export per object but does not have the option for DROP/CREATE (how dumb!). Any other suggetions?

Todd Smith

[Marcus_Idle]

Ironically, while MS SQL can't do this, the MySQL Migration Toolkit will do this for you.

[Marcus_Idle]

Actually I posted too soon; not sure if it does stuff like stored procedures. Worth checking out though.

[Brady Kelly]

A few days ago I donwloaded an MS app that scripts databases with data and structure. I think it's called the Database Migration Wizard. Maybe that can help.

il

[syoungstephen]

Hi, At my work we use DB Ghost[^] by Innovartis. It's not free, although I thought the Scripter bit used to be, but it scripts each object in a separate file with DROP and CREATE. Steve

[reshi999]

I have used Apex SQL[^] before and its pretty good, is scriptable from a command prompt to.

[shtru]

You can quickly write a small app that will do just that, using SQLDMO. I did it not long ago in order to put the DB under version control, it worked on SQL Server version 2000 and 2005. You can search for SQLDMO on CodeProject, I think some articles here talk about it. EDIT : or i can send you the code, it's really really short. EDIT2 : this seems to be what you're looking for : http://www.codeproject.com/KB/database/ScriptDatabase.aspx[^]

[Keith Wempe]

Check out www.Eglop.com[^]. It is not free but they have a great tool for Sql Server. Keith Wempe

[Mark J Miller]

Have you looked at SSMS 2008? You don't need to be doing 2008 development but the IDE now allows scripting with DROP/CREATE and you can script one file per object. Plus the wizard is greatly improved over previous versions. I haven't looked but if you wanted something free I'm sure the Express version would include the generate scripts wizard. I really like the IDE improvements in general over 2005, I've upgraded all my machines to 2008 regardless of the target server version because of the features in the IDE.

Code responsibly: OWASP.org Mark's blog: developMENTALmadness.blogspot.com Click here for Free Industry White Papers/Magazines! Bill Cosby - "A word to the wise ain't necessary - it's the stupid ones that need the advice."

[MarkLTX]

Checkout http://www.idera.com/Downloads/Default.aspx[^]. Idera has many SQL Server products and toolsets. Not free, but with a 14 day evaluation period. Mark L.

[Member 4347358]

SQL Script Builder is a nice tool for this. It also includes functionality to grab the data as well if you like and is freeware. www.sqlscriptbuilder.com

[Fabio Franco]

You can do it yourself with libraries provided by the .Net Framework, the following C# code snippet will script the whole database, it is up to you how to put that on files, separate files, same files or on a textbox like my example. And yes, you have the DROP/CREATE option in this case. Very useful library:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
using Microsoft.SqlServer.Management.Common;

namespace DBScripter
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
ServerConnection conn = new ServerConnection(new System.Data.SqlClient.SqlConnection(@"Your Connection String"));

        Server srv = new Server(conn);

        Database db = srv.Databases\["Your\_Database"\];

        Scripter spt = new Scripter(srv);
        spt.Options.ScriptDrops = false;
        spt.Options.WithDependencies = true;

        Urn\[\] urna = new Urn\[1\];
        
        foreach (Table tb in db.Tables)
        {
            urna\[0\] = tb.Urn;

            if(!tb.IsSystemObject)
            {
                StringCollection sc = spt.Script(urna);
                foreach(string str in sc)
                {
                    textBox1.Text += str + "\\r\\n";
                }

                textBox1.Text += "\\r\\n";
            }
        }
    }
}

}

To use this code you will have to add the following references to your project: Microsoft.SqlServer.ConnectionInfo Microsoft.SqlServer.Smo Microsoft.SqlServer.SmoEnum Hope this helps. Regards, Fábio

[Efrain Villa]

We use SQL Compare from Red Gate and it's an excelent tool for that and other tasks. You can download an evaluation version.

[Glosse]

Are you trying to distribute a database to a client? I just back up the database, zip the .bak file and send it to the client. The client can then restore it to their server. Do you need to generate script for a single proc or table? Just use the SQL Server Management Studio (or Enterprise Manager) built-in scripting function. Works well.

[Todd Smith]

This is what I ended up doing. Scriptio has everything in it but I just wanted a streamlined version I can run from the command line so I hacked my own.

Todd Smith

[Kishore P]

Hi, Already I have developed this type of tool. Send your personal E-Mail, I'll send the application.

[Todd Smith]

This is for putting the DB into source control.

Todd Smith