Click here to Skip to main content
15,867,834 members
Articles / Database Development / SQL Server
Tip/Trick

Code Generator for SQL Server

Rate me:
Please Sign up or sign in to vote.
4.78/5 (6 votes)
8 Jan 2020CPOL3 min read 23.3K   1.1K   19   4
Tool on T-SQL to code basic generator for SQL Server

Introduction

Developing an application from scratch in record time and with the minimum functionalities is a great challenge today for developers in the database. We need tools that help us develop faster and easier according to our needs. It helps us to build a simple skeleton to implement database objects faster to add logic as needed for the application, this code can be customized or can add new functionalities.

Background

There are many tools for generating or creating code for SQL Server, many are free to use and others are paid. However, these types of tools are limited in the sense that you cannot modify or add new features according to your needs. For the development of our projects, the intention of this code is that you can build faster, easier and integrate more functionalities as you need them.

The code generator for SQL Server is developed with Transact-SQL in DML (Data manipulation language). It does not depend on other software for its operation or obtaining SQL code, where you can modify or add new functionalities to the code for your needs and objectives. With the aim of developing a database object faster without wasted time, the code is generated by table in this first version, the database objects that are generated are the following:

  • Stored Procedures:
    • Each Stored Procedure includes a header: Author, version control, object description, comments.
    • Each Stored Procedure contains the control and / or handling of errors.
    • Load with two statements:
      • Insert statement: This occurs when the identity of the table is a parameter equal to zero is sent.
      • Update statement: This occurs when the identity of the table is a parameter sent that is greater than zero.
    • Select:
      • Select statement from all records: This occurs when the identity of the table is a parameter equal to zero is sent.
      • Select statement a record: This occurs when the sending of the identity of the table is a parameter that is greater than zero.
  • Extended properties for each column of the table.
  • Script Select statement with only “Inner Join” clause with parent tables.
    • The first Select statement obtains all column names with their aliases from each parent table; The aliases go by alphabet.
    • The second Select statement obtains all columns "using the *"; aliases of each parent table go by alphabet.
  • Script Select statement with only “Inner Join” clause with child tables.
    • The first Select statement obtains all column names with their aliases from each parent table; The aliases go by alphabet.
    • The second Select statement obtains all columns "using the *"; aliases of each parent table go by alphabet.
  • Create backup table or movements record table.
  • Trigger to insert movement in the backup record table.
    • Before inserting statement: Data as originally.
    • After inserting statement: New data updated.
  • Scripts to test the created objects.
    • Test Insert statement in the main table.
    • Test update statement in the main table.
    • Test Delete statement to the main table.
    • Proof of stored procedures Load and Select.
    • Proof of stored procedure select with identity of the table is greater than zero.
    • Proof of stored Procedure select of all rows with identity of the table is equal to zero.

Using the Code

It is easy and simple to use the SQL Server code generator script, it does not imply making large configurations, because it is developed in Transact-SQL for SQL Server, with the following steps:

  1. Open an instance of “Microsoft SQL Server Management Studio” (SSMS).
  2. Open the .sql file: "Code generator script for SQL Server.sql".
  3. Enter the name of the "Database" where it contains the table to generate the desired code.
  4. Enter the name of the "Table".
  5. Optional, you can add "comments" or leave it empty.
  6. Click on the "F5" or "Execute" key, the SQL Server code generator script will be executed.
  7. Get a "result" to take the code snippet of interest for your work.

Image 1

Points of Interest

  • Working to improve the code and add new functionalities for the generator code for SQL Server
  • Working on code generators for other databases, such as MySQL

History

  • 8th January, 2020 - Code Generator for SQL Server - Version 1.1.0 - First version

License

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


Written By
Software Developer (Senior)
Mexico Mexico
Computer Engineer

Comments and Discussions

 
BugAdditional Bug Pin
Member 1012188010-Jan-20 10:06
Member 1012188010-Jan-20 10:06 
QuestionFails to compile (but nice work!)... Pin
Member 873926910-Jan-20 6:39
professionalMember 873926910-Jan-20 6:39 
QuestionCode has at least one flaw... Pin
Member 109813859-Jan-20 12:35
Member 109813859-Jan-20 12:35 
QuestionNOLOCK Pin
Phil Parkin9-Jan-20 6:03
Phil Parkin9-Jan-20 6:03 

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.