By: Greg Robidoux | Updated: 2020-07-29 | Comments (9) | Related: 1 | 2 | 3 | > Database Design
Problem
When creating a new application that requires a data model to support it, the best approach is to use a data modeling tool to develop the logical and then physical data model. Although this is the best approach, not everyone does this for a full blown application let alone for a small utility application or even for add-on features to an existing database.
The development process along with the database design becomes an iterative process where things are developed and then a certain milestone is hit and the database tables need to be modified to accommodate the change. Again, using a tool to assist with the data modeling is the best approach, but here is a simple approach to generate table structures, use them as they are and then regenerate them when a change is needed.
Solution
Taking the programmatic approach is usually the best way to generate code for either tables, indexes or even stored procedures. This could be done by using VB, C, C#, ASP, etc... Another way of doing this is by just using a simple Excel spreadsheet.
In the past I have had the need to generate tables for testing new approaches for data loading or even application prototypes. One simple approach has been to just use Excel, fill out a grid and then generate a table structure based on the parameters that were entered.
Below is a example creating a table called "dbo.Contact".
The spreadsheet is setup as follows:
| Column | Purpose |
|---|---|
| Table | This is used for grouping tables together. If you only use this to generate one table at a time, this is really not necessary. But you could use this to have data for several tables. You could then sort your data to see if you have like columns to make sure the column names are consistent and the datatypes used are the same. |
| Order | This allows for sorting of the data within Excel. This way if you want to sort by the Type, you can do this and then sort again by Order to generate the code. |
| Column | This is the name of the column. As you can see I have other information in some of the columns to specify commands. |
| Type | This is normally used to determine the datatype. For a list of valid datatypes click here. As you can see I also used this for other commands such as the table name in rows -3, -2 and 0. |
| Length | This is the length of the column. This is only needed for the character type fields such as char, varchar, nchar, etc... |
| Options | These are additional options like for setting the column to be an identity value or specifying that the value can be NULL. |
| Terminator | This is the terminator for the end of each line when the command is generated. Basically this is only needed to separate each column in the table create statement. |
| Output | This is the output of the command. To run this you would select the data from row -3 to row 1000 and paste into Query Analyzer to generate the table. This is the only column in the spreadsheet that has a formula and all that it is doing is appending the different values from each column together to create the proper T-SQL statement. |
The results from the above are shown below. This is what gets pasted into Query Analyzer to build the table.
DROP TABLE dbo.Contact
GO
CREATE TABLE dbo.Contact (
ContactID int identity(1,1),
ContactOwnerID int NULL,
FirstName varchar (30) ,
LastName varchar (30) ,
Account varchar (30) NULL,
Title varchar (30) NULL,
Department varchar (30) NULL,
Birthdate datetime NULL,
ReportsTo varchar (30) NULL,
LeadSource varchar (30) NULL,
Phone varchar (15) ,
HomePhone varchar (15) NULL,
MobilePhone varchar (15) NULL,
OtherPhone varchar (15) NULL,
Fax varchar (15) NULL,
Email varchar (30) ,
Assistant varchar (30) NULL,
AsstPhone varchar (15) NULL,
EmailOptOut char (1) NULL,
MailingAddress1 varchar (30) NULL,
MailingAddress2 varchar (30) NULL,
City varchar (30) NULL,
State char (2) NULL,
Zip varchar (10) NULL,
CreatedByID int ,
CreateDate datetime ,
ModifiedByID int ,
ModifiedDate datetime ,
Comments varchar (2000)
)
GO
As you can see this generates code to create a simple table structure. This is a good way to create your base tables to make sure your application is working as planned during the development stages. If you need to make a change, update the spreadsheet and then copy and paste the code to regenerate the table. One thing it does not do is preserve any sample or test data, so this is something else you may need to script to make this process easier when you regenerate the tables.
Next Steps
- Download the sample Excel spreadsheet (xls file)
- Modify the spreadsheet to be a bit more dynamic, add in indexes, primary keys, etc...
- Look at ways of creating tools like this using Excel, ASP, VB, etc... to simplify generating code as well as making repetitive tasks easier
About the author
Greg Robidoux is the President and founder of Edgewood Solutions, a technology services company delivering services and solutions for Microsoft SQL Server. He is also one of the co-founders of MSSQLTips.com. Greg has been working with SQL Server since 1999, has authored numerous database-related articles, and delivered several presentations related to SQL Server. Before SQL Server, he worked on many data platforms such as DB2, Oracle, Sybase, and Informix.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-07-29
