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:

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.

Answers


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.


Need Your Help

Bundler is being blocked by Rubygems website using RVM with Ruby 2.3.0

ruby jenkins rubygems rvm bundler

I am using Jenkins to automate a build process. I am pulling code from git, then running a bundle install to load the correct gems. However when I do so, I am getting an error message (see below)

Android keep BroadcastReceiver while the app is closed

android broadcastreceiver android-service background-process android-broadcast

I use BroadcastReceiver of ACTION_BATTERY_CHANGED to get an event every time the battery is 50%. I can't declare ACTION_BATTERY_CHANGED in manifest, so I declared the BroadcastReceiver in a Service (