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

Popular posts from this blog

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

CRM Portals OnChange Event For DateTime

Occasionally when working with CRM Portals you may run into the need to get the OnChange event for a date time field.  Because of the way that CRM Portals renders date time fields this is slightly more complicated then working with text boxes, check boxes and pick lists.  During my process I ended up reaching out to Microsoft for help on this and below are the steps they provided to help me with this problem: 1) Click on the date time control 2) Press F12 3) In console type $('div.control') and hit enter (this will give you a list of div controls) 4) Locate the div control for the date time field 5) Go to the entity form or web page in CRM and add the following code snippet (replace the 2 with the number your div control is located at): 1: $(document).ready(function () 2: { 3: var dpcontrol = $('div.control')[2]; 4: $(dpcontrol).on("dp.change", function (e) 5: { 6: alert("On chang

HttpWebRequest For Scribe Online Api

Scribe has made it extremely easy to work with their API.  Currently, in my limited free time, I am working on a reusable C# library to send and receive data from Scribe's API.  Once it is completed for Version 1, I will list it to GitHub, so anyone can consume it and provide feedback on improvements.  There will be a fully built out wiki with how to consume the library with references to the Scribe API documentation.  Also there will be both TDD (Test Driven Development) and BDD (Behavior Driven Development) projects.  This way you can see examples of how to consume the library as well as what I tested before release.  If you are not familiar with BDD, I recommend you check out my blog post in my Dynamics 365 blog , that gives a high level overview of it. Anyways, back to what this post is about.  While working on the library, I came across one small issue around TLS 1.2 and making sure that the web request uses it.  Luckily, this is really easy to set in C#, when using .NET 4.