Skip to main content

SQL Script to Create Enums For Table Columns

While working on a data migration project, using C# and Azure Functions, I found myself needing to create enums to use in my code for the columns.  Some of the tables where vary large (over 200 columns) and doing this manually would have taken a while to do it.  So, I wrote the below SQL script to create the enums for me.  To use this simply replace the table and schema variables with the schema and table name you want to generate the enums for.


 declare @Table sysname = '<INSERT TABLE NAME HERE>';  
 declare @Schema sysname = 'INSERT SCHEMA NAME HERE';  
 declare @Result varchar(max) = 'public enum ' + @Table + 'Columns' + '  
 {'  
 select @Result = CONCAT(@Result, '  
      ',ColName, ' = ', SUM(ColNum), ',')  
 from  
 (  
      select COLUMN_NAME as ColName, SUM(ORDINAL_POSITION - 1) as ColNum  
      from INFORMATION_SCHEMA.COLUMNS  
      where TABLE_NAME = @Table AND TABLE_SCHEMA = @Schema  
      group by COLUMN_NAME  
 ) t  
 group by ColName, ColNum  
 order by ColNum  
 set @Result = @Result + '  
 }'  
 print @Result  

Comments

  1. IS THE NET FULL STACK DEVELOPER PERFORMING ALL TASKS? The Net full stack developer demand is already at its height and still increasing. Also, the Net full stack developer performs the functions of integrating the front-end side and the server-side development. So, most tasks are executed by a Net full stack developer.

    ReplyDelete
  2. Web applications generally use SQL inquiries with client-provided input in the WHERE provision to recover information from a data set. https://onohosting.com/

    ReplyDelete
  3. That is the place where the arrangements modeler can help. They will likewise need to work near characterize the information word reference that the framework will utilize. Information components ought to be characterized reliably all through the framework and the hotspot for these definitions should be the database information word reference.https://hostinglelo.in/

    ReplyDelete
  4. The tire market has different sizes, styles, and brands, and understanding which tire can work for your vehicle may be confusing. We provide exceptional tire services from tire replacement, tire rotation, and everything your vehicle needs.

    ReplyDelete
  5. He is a well-respected businessman/leader in the community and has received many awards for various competitions in his field. His son Franco continues to follow in his footsteps. In keeping up with business ethics and determination. Franco strives for excellence, perfection and quality.

    ReplyDelete
  6. She finds great satisfaction in bringing focused medicine, health, and wellness, to her patients. Dr. has a personable and intimate rapport with her patients throughout their inner and outer health journey. She has enjoyed counseling patients and has addressed patients beyond the scope of traditional gynecology. In addition, Dr. cares for men in addressing their hormonal imbalances.

    ReplyDelete
  7. Williamson Caterers offers a variety of menus and packages including All-Inclusive Wedding Packages, Station Menus, Cocktail Party Selections, Brunch and Buffet Menus, and Cook-Outs. Williamson Caterers welcomes the opportunity to customize a menu specific to your event budget and needs.

    ReplyDelete
  8. Buildrite has a fine and friendly approach towards its clients. Their main focus remains satisfied and pleased customers that are acquired through reliance, integrity, proficiency, and high-quality services.

    ReplyDelete
  9. Our expert designers are dedicated to producing layouts that ensure your design is distinctive to our clients' liking as well as functional. We warmly invite you to browse our website or visit our freshly remodeled showroom to witness for yourself how Cabinet & Stone City can provide luxury without the cost!

    ReplyDelete
  10. We are a full-service trucking company established worldwide to add convenience and pace in freight transportation for commercial purposes. Maintaining the safest driving records on the road.

    ReplyDelete
  11. Fame Jackets have so far served many of customers and thus we can claim to have customers wearing our jackets throughout the world. To make sure smooth and efficient shipping and delivery solutions Fame Jackets collaborate with the premium courier services working internationally.

    ReplyDelete
  12. World's Smartest email List Cleaning Service, Quick, simple and accurate. Bulk Mail Verifier will not only save you time but tons of money too!

    ReplyDelete

Post a Comment

Popular posts from this blog

Validating User Input In CRM Portals With JavaScript

When we are setting up CRM Portals to allow customers to update their information, open cases, fill out an applications, etc. We want to make sure that we are validating their input before it is committed to CRM.  This way we ensure that our data is clean and meaningful to us and the customer. CRM Portals already has a lot validation checks built into it. But, on occasion we need to add our own.  To do this we will use JavaScript to run the validation and also to output a message to the user to tell them there is an issue they need to fix. Before we can do any JavaScript, we need to check and see if we are using JavaScript on an Entity Form or Web Page.  This is because the JavaScript, while similar, will be different.  First, we will go over the JavaScript for Entity Forms.  Then, we will go over the JavaScript for Web Pages.  Finally, we will look at the notification JavaScript. Entity Form: if (window.jQuery) { (function ($) { if (typeof (entityFormClientVali

Dynamics Set IFrame URL - D365 v8 vs. D365 v9

While doing client work, I came across a problem with setting an IFrame URL dynamically.  The underlying issue was that the sandbox instance is on v8 of Dynamics 365 and production is on v9 of Dynamics 365.  The reason for this was because this client was setup around the time that Microsoft rolled out v9.  Anyways, JavaScript that I wrote to dynamically set the URL of the IFrame wasn't working in the v9 instance.  This was because of changes that Microsoft made to how IFrames are loaded on the form and also changes to JavaScript. Here is my v8 setup: JavaScript runs OnLoad of contact form.  This works because of how IFrames are loaded in v8.  You can also run it on either a tab change (hide / show) or OnReadyStateComplete event of the IFrame.  Depending on your setup you will need to choose which is best for you.  For me in this case it was the OnLoad event. Here is the JavaScript: function OnLoad() { //Get memberid var value = Xrm.Page.data.entity.attri

Report Authoring Extension Updated To Support Visual Studio 2019

 FINALLY, Microsoft has pushed an update to the Report Authoring Extension for Dynamics 365 to allow for use of Visual Studio 2019.  This update went live on 12/18/2020 and can be found here .  Installing won't be straight forward into Visual Studio 2019, even if you install everything when you first installed it.  This is because SSDT (SQL Server Data Tools) is installed but, SSAS, SSIS and SSRS are all separate modules for SSDT that you have to install separately.  While you should be fine with just installing SSRS to make this work, I would recommend just installing all 3 parts.   All of these can be found in Microsoft documentation .  In the "Install SSDT with Visual Studio 2019" section, you will see a link to the marketplace where you can download the extensions and install them.  Once that is done, download the Report Authoring Extension and run the installer.  That is it, you can now work on SSRS within Visual Studio 2019 for Microsoft Dynamics.