Writing a template with database metadata

One of the most common uses for code generation is building code from a database schema. CodeSmith Generator supports this with SchemaExplorer, a library of types for working with database metadata, plus designers that let you pick database objects from the property sheet.

In this tutorial, you combine SchemaExplorer with template code to generate a stored procedure that returns any table you choose as JSON. You write the template in Visual Basic in the Template Editor in Visual Studio.

Steps

1. The scenario: the T-SQL you'll generate and why it suits a template.
2. The desired SQL statements: start from an example of the output.
3. Create the template in the Template Editor.
4. Set up enumerated properties for values with a fixed list of choices.
5. Set up a SQL property so the user can pick a table.
6. Write the database code that lists the table's columns.
7. Test the final result.

To learn more about SchemaExplorer, see Using SchemaExplorer.

Next: The scenario: a stored procedure that returns JSON