Using SQL Sprocket to generate TSQL Stored Procedures

SQL Sprocket is an open source template-based code generating tool designed specifically for SQL Server Database Developers. I have been using this tool for years to enforce standards, reduce errors, and quickly generate any stored procedures required by my development projects.

How does SQL Sprocket work?

The output generation is based on three types of templates, and the list of tables found in your database:

• Table List Statement – User Mode
• Metadata Templates – Advanced Mode
• Dictionary Templates – Advanced Mode
• Output Templates – User Mode

Table List

You have full control over the way your database tables are listed in SQL Sprocket. Use the default or update the SQL that populates the list of database tables to your preference.

Metadata Templates

Metadata templates give you control over how the data is queried from the SQL Server database and the information provided to the Dictionary Templates. If Microsoft makes any changes to the SQL Server Information_Schema views in a later release just make a quick update to the Metadata Template and you are back to work.

Dictionary Templates

Most users will never need to touch the Dictionary or Metadata Templates, but the flexibilty to change how the output is generated using C# or TSQL is available If you need more output types or just want to change the default processing, Dictionary Templates make it possible to tailor the output generation to your needs.

Output Templates

Output Template allow you to use the data collected from your database schema along with the <OUTPUT> parameters to generate your database code the way you want it.

How easy is it to use?

There is no complex syntax to learn, and you can start generating output based on your SQL Server database in minutes.

1. Start the tool and connect to your SQL 2012, 2005, or 2008 database.
2. Create the Output templates for your specific development needs or use the TSQL templates provided.
3. Select the tables and templates you want to use to generate code.
4. Click Execute and generate your code.
5. Copy the code to a script or compile it against your database.

SQL Sprocket allows you to define your own generation templates using C# code or TSQL, but did not ship with a TSQL generator out of the box. As a contributor to the SQL Sprocket project, I am pleased to announce the availability of a an example TSQL generator for SQL Sprocket.

I have published both my TSQL generator file and all the output templates I created for the Fulcrum SKMS project. The plan is to provide future templates and updates on the SQL Sprocket Project download page as they are developed.

I hope you will give SQL Sprocket a try and let us know what you think.