Code Generator For SQL Server

Introduction

Developing an application from zero 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 to add new functionalities.

Background

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

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 tables 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 sent is a parameter equal to zero.
- Update statement: This occurs when the identity of the table sent is a parameter greater than zero.
Select
- Select statement from all records: This occurs when the identity of the table sent is a parameter equal to zero.
- Select statement a record: This occurs when the sending of the identity of the table is a parameter 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.
- Test stored procedures Load and Select.
- Proof of stored procedure select with identity of the table is greater than zero.
- Proof 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,

Open an instance of “Microsoft SQL Server Management Studio” (SSMS).
Open the .sql file: "Code generator script for SQL Server.sql".
Enter the name of the "Database" where it contains the table to generate the desired code.
Enter the name of the "Table".
Optional, you can add "comments" or leave it empty.
Click on the "F5" or "Execute" key, the SQL Server code generator script will be executed.
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

Code Generator for SQL Server - Version 1.1.0 - January 2020 - First version

3 Comments

Join the conversation! Your thoughts help the community grow.

navin6y ago
Great Article. Thank you. for my case, I replaced this line INNER JOIN sys.columns cfk ON tfk.object_id = cfk.object_id AND cfk.column_id = referenced_column_id with this INNER JOIN sys.columns cfk ON tfk.object_id = cfk.object_id AND fk.parent_column_id = cfk.column_id
Sourav Kumar Das6y ago
Nice article.
Rushi Mehta6y ago
Nice... Good Start