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

Question

I want to generate scripts for database objects, for example.

• tables
• view
• stored procedures
• Functions

FROM

• SQL Server Management Objects (SMOs)
• SQL Distributed Management Objects (SQL-DMO) (stripped)

not installed with a new installation:

• Windows XP
• Windows Vista li>
• Windows 7

and they are not distributed, they are not an option (it will work on the client’s machine).

( EDIT : It looks like SMO is actually distributable to date.)

Is there any source code that converts SELECTs from system tables to related scripts?

---

I will start with pseudocode that scripts 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 that can use only one 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 to servicesharvest316 for pointing out sp_helptext . That is why I have a class that abstracts these things.

Answer 1

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

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

Answer 2

Have you tried sp_helptext?

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

Answer 3

Open DBDiff (link here ) implements a GUI for comparing databases and generating update scripts. It also includes a command line tool. You can use the guts of the project to create CREATE TABLE statements.