CodeSmith for Creating SQL Tables

I was creating a SQL Server 2005 table today in SQL Server Management Studio and went to the bottom pane to create my Identity field like I always do.  I scrolled down, scrolled some more, and selected “yes” from the dropdown to confirm it was indeed an identity field when I thought, “This is insane.”  I’ve been creating a lot of SQL tables recently and I figure in the foreseeable future I’ll be creating a bunch more.

“With God as my witness, I won’t use SQL Server Management Studio to create a table ever again!”  Give’m hell, Vivian.

Time for–you guessed it–a CodeSmith Template to create a SQL script to do the job for me, to create the entire table, primary key, default values, drop/create, the works.  What was the most efficient process for entering field information?  We needed a field name, its datatype, its null state, and optionally, its size.  We also needed to handle default values.  That’s it for now.  Foreign keys and other SQL flourishes are for another time.

CodeSmith supports a StringCollection input type, which you see in action below.  This is perfect for a series of field descriptions, which we will space-split into a string array, passing the individual string arrays to our CodeSmith methods.  “K” will stand for the primary key field.  “BeerID K.”  Done with that.  Beats the heck out of scrolling for the Identity dropdown in SQL Server Management Studio!  For an integer we’ll use “I” and its null state (“N” or “NN” for null or not null.)  “BeerType I NN.”  (Integer and not null.)  Okay, skipping ahead we’ll use “BeerName NV N 65.”  (NV for NVarchar.)  We’ll need a size on that one.  Next, “B” for bit for our “IsAFave B NN.”  Not null.  I rarely meet a beer I don’t like so we’ll want a default value of “1” for that one which we’ll add below.

The execution of the CodeSmith template to create my dbvt_beer table is shown above, with CodeSmith Studio and some of the code that creates the SQL table fields in the background.

We’re using a second StringCollection for Column Defaults, so a copy-n-paste from the TableColumns StringCollection Editor and we’ll enter our default values.

BeerManufacturer I 1
IsAFave B 1

We’re specifying “1” as the default value for our BeerManufacturer Integer field, and “1” for true for our IsAFave Bit field.  We click “GENERATE” and we’re home.  The SQL script generated is shown below without any modification.  And yousa-yousa, we’ve got “Command(s) completed successfully” in our SQL results window!

Article written by

A long time developer, I was an early adopter of Linux in the mid-90's for a few years until I entered corporate environments and worked with Microsoft technologies like ASP, then .NET. In 2008 I released Sueetie, an Online Community Platform built in .NET. In late 2012 I returned to my Linux roots and locked in on Java development. Much of my work is available on GitHub.