One of the coolest things I have found for automating code generation is the use of the INFORMATION_SCHEMA views found in many data engines. They can be extremely useful when you have to write repetitive code based on your database schema.
For instance, using Entity Framework, you can generate your tables using database first or code first techniques. Frankly, it doesn’t matter. Once your tables are created, you can use the meta data found in the INFORMATION_SCHEMA views to generate EF code for rest calls or repository calls as are commonly found in REST controllers.
INFORMATION_SCHEMA.TABLES is a good view for when you wish to generate code, but only need the TABLE_NAME.
Because INFORMATION_SCHEMA.TABLES returns both tables and views, make sure you check the property to assure you are getting the type of data you wish, View or Table.
If you need to have column specific data included in your code, use the INFORMATION_SCHEMA.COLUMNS view. It provides a great amount of information about each column including the name, data type, nullability, character length, numeric specifications if the data type is a user defined DECIMAL, and much more.
I find I can create a basic template regarding how I want the application code to be written and store it in a string. Then, in my query, I can use the REPLACE function to replace place holders in my code script, using attributes from the view being used.
This method works much more efficiently and accurately than the typical cut, paste, and replace method usually used in code. That is especially true when you must replace a number of attributes when creating a new copy of the code, and some of the text values you replace overlap.
Do you use INFORMATION_SCHEMA views, or other system views to assist in writing accurate code? Get in the conversation by sharing some of your code generation scripts in our comments, or even uploading them to our scripting section.
Cheers,
Ben