SQL Server: How to generate object scripts without

问题:

i want to generate scripts for database objects, e.g.

• tables
• views
• stored procedures
• functions

Since:

• SQL Server Management Objects (SMO)
• SQL Distributed Management Objects (SQL-DMO) (depricated)

are not installed on a fresh install of:

• Windows XP
• Windows Vista
• Windows 7

nor are they redistributable, they are not an option (it will run on a customer's machine).

(EDIT: It looks as if SMO is actually redistributable as of today.)

Is there any source code that converts SELECTs from system tables into associated scripts?

---

i'll start us off with the pseudo-code that scripts a stored procedures, views, triggers, or user-defined functions:

String GetStoredProcedureScript(String storedProcedureName)
{
   return GetHelpText(storedProcedureName);
}

String GetViewScript(String viewName)
{
   return GetHelpText(viewName);
}

String GetTriggerScript(String triggerName)
{
   return GetHelpText(storedProcedureName);
}

String GetUserDefinedFunctionScript(String userDefinedFunctionName)
{
   return GetHelpText(userDefinedFunctionName);
}

All which can internally use a single helper function:

String GetHelpText(String objectName)
{
   String szQuery = 'EXECUTE sp_helptext '+QuotedStr(objectName);


   String sql := '';

   using (Recordset rs = connection.Execute(szQuery))
   {
      while not rs.EOF do
      {
         sql = sql+rs['text'];
         rs.Next;
      }
   }

   return sql;
}

Edit: Thanks servicesharvest316 for pointing out sp_helptext. That's why i have a class that abstracts these things away.

回答1:

This is the book for you. It explains how to make a code generator that will do what you asked.

I use a modified version for MySql and it worked like a charm. Code Generation in Microsoft .NET

回答2:

Have you tried sp_helptext?

String szQuery = 'EXEC sp_helptext '+QuotedStr(storedProcedureName)

回答3:

Open DBDiff (referenced here) implements a GUI for comparing databases and generating an update script. It also includes a command line tool. You could use the guts of the project to generate CREATE TABLE statements.