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
Post a Comment