SQL Server: How to generate object scripts without DMO/SMO?

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 viewNam开发者_如何学Ce)
{
   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.

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

Have you tried sp_helptext?

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

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.