Click here to Skip to main content
13,348,874 members (78,312 online)
Click here to Skip to main content
Add your own
alternative version


51 bookmarked
Posted 5 Apr 2006

Build SQL Server Stored Procedures From Information_Schema Tables

, 5 Apr 2006
Rate this:
Please Sign up or sign in to vote.
Save hours of time by creating stored procedures and code from database metadata.

BuildCode application window


Like so many of you, I depend on CodeProject, and the amazing community of brilliant and generous people who fill its pages, to get me out of tight spots on a weekly (if not daily) basis. And now it is time for me to give something back.


How much time gets wasted hand-coding stored procedures, classes, and other code related to database tables? One of the most tedious and time-consuming tasks in any project can be the creation of standard Insert, Update, Delete, and Select stored procedures.

The application I am offering here allows the user to select a database, select a table from that database, and automatically generate all of these standard stored procedures in seconds, generally without fear of a syntax error.

There is nothing new here. There are certainly applications you can buy, that will do something similar in a far more elegant way. But, hey... this is free!

Using the code

The first step for you will be to modify the config file. The appSettings section has key/value pairs where the key is the name of a SQL Server database you have access to, while the value contains a connection string to that database. That's the hard part. Pay no attention to the "Northwind" setting for the time being.

<?xml version="1.0" encoding="utf-8" ?>
        <add key="LoanAbstract" 

          value="server='SVR-A-XYZ';user id='ReaderWriter';
        <add key="Northwind" 


Once you've done this, open the application. The left-most drop down list should now contain the database name(s) from the appSettings section. Select one, then click on the "Show Tables" button. This will populate the drop down on the right with all the tables in that database (assuming the connection string is correct). Select a table.

Now, choose from one of the radio buttons (the SQL Insert, for example), and hit the "Build Code" button. What you should now see in the text box below is a syntactically-correct Insert stored procedure for the selected table. And the same should be true for all the other radio buttons. Each of them represents a separate class within the application.

Hit the "Copy to Clipboard" button, then paste the result into the Query Analyzer, make any necessary changes, and you are done. Or, select a folder by hitting the "Select Output Folder" button at the bottom left, and write the code out to a file. (The files that are written have a name beginning with the table name, followed by the code type that is in the text box, followed by a timestamp. So, if the table is "Address", and the code type is "SQL Insert", the file name will look something like this: "Address_SqlInsert_20060404_161216.txt".)

At some point, you will want to go into the various classes, making changes, or creating new classes to build code according to your needs. One of the first things you will probably want to do is replace my name and user ID throughout with yours. And if my decidedly idiosyncratic style of writing stored procedures (yes, this is the way I hand-code them!) annoys you, go in and change them.

Each class has three methods to build the code. The first, BuildCodeStartOfLoop, builds the table-level part of the code. Similarly, to supply any code at the end of the process is the BuildCodeEndOfLoop method. In between, building code for each column in the table, is the BuildCodeEachColumn method.

I hope you find this application useful, saving you time for more interesting pursuits.


If you are like me, you won't want your connection strings hanging out in plain code in the config file. I always encrypt the connection string (see the Northwind example in the code above; you were wondering about that, weren't you?), and decrypt it when it is pulled into the application. See the commented out code in the GetConnectionString method in frmBuildCode.cs. The encryption class code is included, and was developed initially by Frank Fang, and shared with the rest of us in an article in CodeProject[^]. The included Encrypter class contains all of the improvements recommended by other readers of that article. I have used it successfully for quite a while without any hint of error.

Likewise, the DataProvider class included in this application was strongly influenced by an interesting article[^] (actually, a trio of articles) by David Veeneman. If a method requires SqlParameters, I build them in the calling method (see the SQL Parms code type), load them into an ArrayList, and pass them to the appropriate DataProvider method. Data that is pulled back in a DataSet object using a FillDataSet method then gets reloaded into an instance of a class based on the table (see the C# class code type). The Table, TableDAO, and DataProvider classes demonstrate some of the concepts in the Veeneman articles.


I truly hope this application takes some of the drudgery from your life. And to all those who contribute in any way to CodeProject, my most sincere thanks!


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Dwight Johnson
United States United States
No Biography provided

You may also be interested in...


Comments and Discussions

QuestionCant get this working... Pin
DumpsterJuice20-Aug-15 3:45
memberDumpsterJuice20-Aug-15 3:45 
AnswerRe: Cant get this working... Pin
Dwight Johnson20-Aug-15 3:49
memberDwight Johnson20-Aug-15 3:49 
GeneralRe: Cant get this working... Pin
DumpsterJuice20-Aug-15 4:00
memberDumpsterJuice20-Aug-15 4:00 
GeneralRe: Cant get this working... Pin
Dwight Johnson20-Aug-15 4:04
memberDwight Johnson20-Aug-15 4:04 
GeneralSQL2008 Express Pin
bigdavelamb11-Nov-09 10:29
memberbigdavelamb11-Nov-09 10:29 
GeneralRe: SQL2008 Express Pin
Dwight Johnson11-Nov-09 10:35
memberDwight Johnson11-Nov-09 10:35 
GeneralRe: SQL2008 Express Pin
bigdavelamb11-Nov-09 12:07
memberbigdavelamb11-Nov-09 12:07 
Hi Dwight

Sorry, yes I sorted it before I read your post. Should not of been so hasty with the post.

Thanks for your help though anyway, it's a really great little tool you have written.

GeneralRe: SQL2008 Express Pin
Dwight Johnson12-Nov-09 3:22
memberDwight Johnson12-Nov-09 3:22 
GeneralGreat !! Pin
Member 462523826-Jul-08 9:17
memberMember 462523826-Jul-08 9:17 
GeneralUsed weekly or sometimes daily Pin
inetfly12320-May-08 4:19
memberinetfly12320-May-08 4:19 
GeneralRe: Used weekly or sometimes daily Pin
Dwight Johnson20-May-08 4:34
memberDwight Johnson20-May-08 4:34 
GeneralVery Helpful, Modify @@IDENTITY Pin
glwright1-Apr-08 10:26
memberglwright1-Apr-08 10:26 
GeneralRe: Very Helpful, Modify @@IDENTITY Pin
Dwight Johnson14-May-08 8:13
memberDwight Johnson14-May-08 8:13 
GeneralExcellent ! Pin
LakshmiSuresh32-Sep-07 22:06
memberLakshmiSuresh32-Sep-07 22:06 
QuestionVery usefull..And How About MS SQL 2005? Pin
Domingo M. Asuncion27-Mar-07 21:40
memberDomingo M. Asuncion27-Mar-07 21:40 
AnswerDoes work in SQL Server 2005 Pin
Dwight Johnson14-May-08 8:14
memberDwight Johnson14-May-08 8:14 
GeneralNice Shot..... Pin
Domingo M. Asuncion27-Mar-07 21:39
memberDomingo M. Asuncion27-Mar-07 21:39 
GeneralThat's what I want to do. Pin
vielili12-Apr-06 0:33
membervielili12-Apr-06 0:33 
GeneralDidn't work on SQL Server 2005 Pin
Adam Tibi5-Apr-06 11:50
memberAdam Tibi5-Apr-06 11:50 
GeneralRe: Didn't work on SQL Server 2005 Pin
Dwight Johnson6-Apr-06 3:37
memberDwight Johnson6-Apr-06 3:37 
GeneralRe: Didn't work on SQL Server 2005 Pin
Marc Leger18-May-06 20:27
memberMarc Leger18-May-06 20:27 
GeneralRe: Didn't work on SQL Server 2005 Pin
Dewey17-Jan-08 12:47
memberDewey17-Jan-08 12:47 
GeneralRe: Didn't work on SQL Server 2005 Pin
alhambra-eidos13-May-08 22:40
memberalhambra-eidos13-May-08 22:40 
GeneralRe: Didn't work on SQL Server 2005 Pin
Dwight Johnson14-May-08 8:11
memberDwight Johnson14-May-08 8:11 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.180111.1 | Last Updated 5 Apr 2006
Article Copyright 2006 by Dwight Johnson
Everything else Copyright © CodeProject, 1999-2018
Layout: fixed | fluid