Code Generator for SQL Server

• Download source - 7.3 KB

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.

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

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