Как стать автором
Обновить

«Real» enums for MS SQL Server

Время на прочтение 35 мин
Количество просмотров 19K

Как известно, MSSQL не предоставляет возможность создания/использования перечислений, что зачастую влечет за собой неявный, ненадежный, некрасивый, сложно поддерживаемый код. Можно спорить о том, что в реляционной БД перечисления как таковые места иметь не могут, но множество раз в моей (и не только моей) практике рождались подобные строчки:
select * from Process where ProcessType = 1 /* Suspended */

или

declare @processSuspended int = 1;
select * from Process where ProcessType = @processSuspended;
------или------------------------------------------------------------------------------------------------------
DECLARE @processSuspended INT;
SELECT @processSuspended = Value FROM ProcessEnum WHERE Name = 'Suspended';
SELECT * FROM Process WHERE ProcessType = @processSuspended;
------или------------------------------------------------------------------------------------------------------
CREATE FUNCTION ProcessEnum_Suspended() RETURNS INT AS BEGIN RETURN 1; END;
SELECT * FROM Process WHERE ProcessType = ProcessEnum_Suspended();


И тому подобные костыли разного уровня сложности.
А хочется —
SELECT * FROM Process WHERE ProcessType = EnumProcess.Suspended;


Решение, в общем, известно — делаем CLR тип. Но кому хочется все время писать, публиковать сборки и заботиться об их актуальности?

Цели этой статьи
  • максимально приблизиться к этому ласкающему взор синтаксису
  • автоматически создавать перечисления


Disclaimer: Все дальнейшее писалось с использованием Visual Studio 2012, MS SQL Server 2012 SP1, .Net Framework 3.5, в сжатые сроки и является только примером, как.

Опробуем без лишних слов


(UPD: Все вместе одним скриптом)

1. Импортируем сборку (в конце статьи — ссылка на исходный код), (помним про ALTER DATABASE SET TRUSTWORTHY ON)
CREATE ASSEMBLY SQLAutoEnums...

CREATE ASSEMBLY [SQLAutoEnums]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C0103001EBDFF510000000000000000E00002210B010B00003E00000006000000000000CE5C000000200000006000000000001000200000000200000400000000000000060000000000000000A000000002000000000000030060850000100000100000000010000010000000000000100000000000000000000000745C000057000000006000002803000000000000000000000000000000000000008000000C0000003C5B00001C0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E74657874000000D43C000000200000003E000000020000000000000000000000000000200000602E7273726300000028030000006000000004000000400000000000000000000000000000400000402E72656C6F6300000C0000000080000000020000004400000000000000000000000000004000004200000000000000000000000000000000B05C0000000000004800000002000500142900002832000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001330030050000000010000110F00FE16040000016F1100000A28060000060A7E1200000A0B066F170000060D1613042B180911049A0C07087201000070281300000A0B1104175813041104098E6932E1076F1400000A731500000A2A133002002F000000020000110F00FE16040000016F1100000A28060000060A066F1300000618330C066F15000006281600000A2A14281600000A2A001B3004001F010000030000117207000070731700000A0A066F1800000A72370000701B8D01000001130D110D16028C04000001A2110D17038C04000001A2110D18048C04000001A2110D19058C04000001A2110D1A0E048C04000001A2110D281900000A0B0706731A00000A130B110B176F1B00000A110B0C086F1C00000A0D731D00000A13042B7F09166F1E00000A6F1100000A130509176F1E00000A6F1100000A130609186F1E00000A6F1100000A130709196F1F00000A13081104110511062804000006130911092D26731D000006130A110A11057D10000004110A11067D11000004110A1309110411096F2000000A11097B1300000411071108732100000A6F2200000A096F2300000A3A76FFFFFF11042805000006130CDE0A062C06066F2400000ADC110C2A00411C0000020000000B00000007010000120100000A000000000000001B3002004E00000004000011026F2500000A0C2B281202282600000A0A067B1100000404282700000A2C12067B1000000403282700000A2C04060BDE1B1202282800000A2DCFDE0E1202FE160400001B6F2400000ADC142A072A00000110000002000700353C000E00000000133002001A00000005000011731F0000060A06026F1E0000060B076F1400000A731500000A2A000013300200180000000600001173100000060B07026F12000006070A066F1900000626062A1B30030091000000070000117207000070731700000A0A066F1800000A72DC00007002282900000A0B0706731A00000A13071107176F1B00000A11070C086F1C00000A0D732A00000A13042B35730A0000061306110609166F1F00000A7D01000004110609176F1E00000A6F1100000A7D0200000411061305110411056F2B00000A096F2300000A2DC311041308DE0A062C06066F2400000ADC11082A0000000110000002000B007984000A000000001330020018000000080000110274030000020A03067B010000045404067B02000004512A1E02282C00000A2A1E02282C00000A2A133004009E0000000900001102282C00000A02282D00000A722C010070282E00000A7D0700000402723C010070282F00000A7D08000004021A8D010000010A0616027B08000004A206177246010070A20618283000000A8C26000001A20619724A010070A206283100000A7D09000004021A8D010000010B0716027B08000004A207177246010070A20718283000000A8C26000001A207197252010070A207283100000A7D0A0000042A1E027B030000042A5A02037D0300000402162814000006021428160000062A1E027B040000042A2202037D040000042A1E027B050000042A2202037D050000042A1E027B060000042A2202037D060000042A1B3005002D0100000A00001102168D1A000001281800000602168D27000001281600000602172814000006027B09000004022811000006283200000A733300000A0A066F3400000A027B070000046F3500000A066F3400000A725C010070027B0A000004027B09000004283600000A6F3700000A066F3400000A166F3800000A066F3400000A176F3900000A066F3400000A027B080000046F3A00000A066F3B00000A26066F3C00000A6F3D00000A0B066F3E00000A02188D1A0000010D091672EF010070066F3F00000A13041204284000000A282E00000AA2091707A2092818000006066F3F00000A2D1A02027B0A000004284100000A2816000006021828140000062B0702192814000006066F3F00000A0CDE212602192814000006FE1A027B09000004284200000A027B0A000004284200000ADC082A000000011C000000001F00EB0A010A2E00000102001F00F5140117000000001A721B0200702A4A027B10000004027B11000004282E00000A2A000013300600590000000B000011027B120000042C12027B120000047E1200000A282700000A2C3802027B110000047223020070283000000A0A1200FE16260000016F1100000A722D0200707E1200000A6F4300000A281300000A7D12000004027B120000042A4A02734400000A7D1300000402282C00000A2A1B3005008F0100000C000011734500000A0A06027B140000046F4600000A26036F2500000A1304383F0100001204282600000A0B06027B15000004076F1C000006076F1A0000066F4700000A26077B130000046F4800000A13052B281205284900000A0C06027B160000041202284A00000A1202284B00000A8C2D0000016F4700000A261205284C00000A2DCFDE0E1205FE160600001B6F2400000ADC06027B170000046F4600000A2606027B1A000004076F1B000006076F1C0000066F4700000A26077B130000046F4800000A13062B671206284900000A0D06027B18000004076F1B0000061203284A00000A076F1C0000066F4D00000A2606027B190000041A8D010000011307110716076F1A000006A21107177231020070A21107181203284A00000AA2110719076F1C000006A211076F4E00000A261206284C00000A2D90DE0E1206FE160600001B6F2400000ADC06027B1B000004076F1B000006076F1C0000066F4700000A261204282800000A3AB5FEFFFFDE0E1204FE160400001B6F2400000ADC06027B1C0000046F4600000A26066F1100000A2A00414C0000020000004E00000035000000830000000E0000000000000002000000C400000074000000380100000E00000000000000020000001B000000520100006D0100000E00000000000000033002006A000000000000000272350200707D1400000402729C0500707D150000040272D20500707D160000040272EA0500707D170000040272F20500707D180000040272720600707D190000040272EE0600707D1A0000040272611200707D1B0000040272B81A00707D1C00000402282C00000A2A000042534A4201000100000000000C00000076342E302E33303331390000000005006C0000006C080000237E0000D8080000B809000023537472696E67730000000090120000BC1A0000235553004C2D00001000000023475549440000005C2D0000CC04000023426C6F620000000000000002000001571FA2090902000000FA253300160000010000002F000000080000001C0000001F00000019000000010000004E00000004000000140000000C000000030000000A0000000F0000000600000001000000030000000100000000000A000100000000000600CA00C3000600D100C3000A00F700E2000A000001E2000A002101E20006006F0154010600B701A40106005F0354010A0047042C040600D904BA040600FE04EC0406001505EC0406003205EC0406005105EC0406006A05EC0406008305EC0406009E05EC040600B905EC040600D205EC040600EB05EC0406001B06080657002F06000006005E063E0606007E063E060A009C062C040600BA06C3000A00FC06E6060A001D070A070A003607E6060A0041070A070A004B07D6000A006707E6060A0083070A070600AB07C3001B00BF0700000600F907BA0406002008C30006004308C30006005008C30006005F0855080E00710808060E00790808060600FD08550806001D09550806004B09C30006006509C3000600830977090000000001000000000001000100010010001B0000000500010001000300100030000000050001000A00A00000003B004500000003000B000000100052005E000500030010000101000075005E0009000B001A000000100083009200050010001A0000001000AA009200050014001E000600F60158000600F9015B0001004D025B0001005E0289000100770293000100AF02A2000100C1025B000100CA025B000100D4025B000100DE025B000606F802580056800003890056800C03890056801703890056801F038900060034035B000600F9015B00010055035B0006006E03BF0001009A035B000100A5035B000100B4035B000100C1035B000100D0035B000100E0035B000100F7035B0001000C045B00010021045B0050200000000096000A010A000100AC200000000096002B0111000300E8200000000096003F01180005003022000000009100760127000B009C220000000091007D0134000E00C4220000000091009A013F000F00E822000000009600C301450010009823000000009600D6014B001100BC23000000008618F00154001400C423000000008618F00154001400000000000000C60DFE015E001400000000000000C60D070262001400000000000000C605100267001500000000000000C60D18026B001500000000000000C60D230270001500CC23000000008618F00154001500762400000000E609FE015E0015007E2400000000E609070262001500952400000000E60918026B0016009D24000000008108530283001600A62400000000E609230270001700AE2400000000810866028D001700B724000000008608850297001800BF240000000081089A029C001800C82400000000E601100267001900202600000000860826035E00190027260000000086083B035E0019003C2600000000860848035E001900A126000000008618F00154001900B4260000000086009103CB0019009C28000000008618F00154001A00000000000000000001005904000000000000000001005904000000000000000001005E04000002006804000003007504000004008004000005008D04000001009904000002009E0400000300A50400000100990400000100590400000100AA0400000100B30402000200B70402000300A50400000100E60400000100E60400000100E60400000100E60400000100E604000001009904050010004900F00154005100F00154005900F00162006100F00162006900F00162007100F00162007900F00162008100F00162008900F00162009100F00162009900F0016200A100F0016200A900F001E800B900F001EE00C100F0015400C900F00154000900B1065E00D100C1065B00D100C706F300D100CE06FA001900F001FF002900DA060F01D900F0016200E1002A075400D1002F07AC01E900F001B301F1005707BA01E9007507C0010C00F001540009019007CD0109019907D2010C00A207D7011400F001E4011C00A207D7010901A607F7011101B70754000C00CA071A022400D8072C02D100E40731022400F007F701D1002F0767032C00F00154002C00A207D7010900F001540021010C089203D100C706960329012C089C0331014808A103D100C706A70341016408B4034901F001540049018A08BA03510198086200D1002F07C0035101A50862005101B308C7035101C708C7035101E20862004901F708F70149010A09CC03610128095E0049013209540049013E0967006901B1065E0041015109D20341015E09D803D1006F09E8031C00F00154007901F001540079019109F40379019809FB031C00CA071A023400D8072C021400A5092C021400AD0910043400F007F701790198091504790198091F0408003000AB0008003400B00008003800B50008003C00BA0020008300B00024000B00D5002E001B005B042E002B006D042E007B00AA042E0033006D042E003B005B042E007300A1042E0023006D042E00430073042E004B006D042E0063008B042E006B00980440008300B00044000B00D500600083001B0164000B00D50084000B00D500A4000B00D500E0008300520205011601FB01370245024B0274038D03AD03DD03EE032804040001000500040007000800000034027500000039027900000040027E00000034027500000039027900000040027E000000E702A60000007503750000007F03750000008803750002000B00030001000C00030002000E00050002000F000700020011000900010012000900010014000B00020013000B00020015000D00010016000D00020017000F00010018000F0002001A00110002001B00130002001C001500C601DD01EC0124026D030404048000000100000000000300000000000000450000000400000000000000000000000100BA00000000000400000000000000000000000100D600000000000400000000000000000000000100C300000000000300020000000000003C4D6F64756C653E0053514C4175746F456E756D732E646C6C0055736572446566696E656446756E6374696F6E7300456E756D4D656D6265720049436F6D70696C65720053514C4175746F456E756D7300437363436F6D70696C65720053514C4175746F456E756D732E436F6D70696C65727300436F6D70696C6553746174757300456E756D44657363726970746F720053514C4175746F456E756D732E47656E657261746F72730053696D706C6547656E657261746F72006D73636F726C69620053797374656D004F626A65637400456E756D0053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C43686172730053716C537472696E670053716C4175746F456E756D73547279436F6D70696C650053716C42696E6172790053716C4175746F456E756D73436F6D70696C650053716C4175746F456E756D7347656E65726174650053797374656D2E436F6C6C656374696F6E732E47656E65726963004C6973746031005365617263680053716C4175746F456E756D7347656E657261746546726F6D4C69737400436F6D70696C6549740053797374656D2E436F6C6C656374696F6E730049456E756D657261626C6500456E756D4D656D6265727343757272656E7400456E756D4D656D6265727343757272656E7446696C6C526F77002E63746F72004944004E616D65006765745F436F6465007365745F436F646500436F6D70696C65006765745F537461747573006765745F436F6D70696C6564436F646500436F64650053746174757300436F6D70696C6564436F6465005F636F6465007365745F537461747573005F737461747573007365745F436F6D70696C6564436F6465005F636F6D70696C6564436F6465006765745F436F6D70696C65724D65737361676573007365745F436F6D70696C65724D65737361676573005F636F6D70696C65724D65737361676573005F63736350617468005F74656D7050617468005F636F646550617468005F646C6C5061746800436F6D70696C65724D657373616765730076616C75655F5F004E6F74436F6D70696C656400496E50726F67726573730053756363657373004661696C6564006765745F56616C75655479706500507265666978006765745F4E616D6546756C6C006765745F4E616D65456E756D005F6E616D65456E756D004B657956616C75655061697260320056616C7565730056616C756554797065004E616D6546756C6C004E616D65456E756D0047656E6572617465005F737472486561646572005F737472456E756D486561646572005F737472456E756D4974656D005F737472456E756D466F6F746572005F73747253747275637456616C7565005F7374725374727563744E756D6572696356616C7565005F737472456E756D537472756374486561646572005F737472456E756D537472756374466F6F746572005F737472466F6F746572004D6963726F736F66742E53716C5365727665722E5365727665720053716C466163657441747472696275746500636F6465007461626C654E616D6500636F6C756D6E50726566697800636F6C756D6E4E616D6500636F6C756D6E4D656D62657200636F6C756D6E56616C7565006C69737400707265666978006E616D6500656E756D4E616D65006F626A0069640053797374656D2E52756E74696D652E496E7465726F705365727669636573004F75744174747269627574650076616C75650053797374656D2E5265666C656374696F6E00417373656D626C795469746C6541747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7956657273696F6E41747472696275746500417373656D626C7946696C6556657273696F6E4174747269627574650053797374656D2E446961676E6F73746963730044656275676761626C6541747472696275746500446562756767696E674D6F6465730053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C46756E6374696F6E41747472696275746500546F537472696E6700537472696E6700456D70747900436F6E63617400546F436861724172726179006F705F496D706C696369740053797374656D2E446174612E53716C436C69656E740053716C436F6E6E656374696F6E0053797374656D2E446174612E436F6D6D6F6E004462436F6E6E656374696F6E004F70656E00466F726D61740053716C436F6D6D616E64004462436F6D6D616E6400436F6D6D616E6454797065007365745F436F6D6D616E64547970650053716C44617461526561646572004578656375746552656164657200446244617461526561646572006765745F4974656D00476574496E7433320041646400526561640049446973706F7361626C6500446973706F736500456E756D657261746F7200476574456E756D657261746F72006765745F43757272656E74006F705F457175616C697479004D6F76654E6578740052756E74696D65456E7669726F6E6D656E740047657452756E74696D654469726563746F727900456E7669726F6E6D656E7400476574456E7669726F6E6D656E745661726961626C650047756964004E65774775696400427974650053797374656D2E494F0046696C65005772697465416C6C546578740050726F636573730050726F636573735374617274496E666F006765745F5374617274496E666F007365745F46696C654E616D65007365745F417267756D656E7473007365745F5573655368656C6C45786563757465007365745F52656469726563745374616E646172644F7574707574007365745F576F726B696E674469726563746F72790053746172740053747265616D526561646572006765745F5374616E646172644F757470757400546578745265616465720052656164546F456E640057616974466F7245786974006765745F45786974436F646500496E7433320052656164416C6C42797465730044656C65746500457863657074696F6E005265706C6163650053797374656D2E5465787400537472696E674275696C64657200417070656E6400417070656E64466F726D6174006765745F4B6579006765745F56616C7565000000050D000A00002F63006F006E007400650078007400200063006F006E006E0065006300740069006F006E003D0074007200750065000080A3730065006C0065006300740020007B0031007D0020006100730020005000720065006600690078002C0020007B0032007D0020006100730020004E0061006D0065002C0020007B0033007D0020006100730020004D0065006D006200650072004E0061006D0065002C0020007B0034007D0020006100730020004D0065006D00620065007200560061006C00750065002000660072006F006D0020007B0030007D00004F730065006C006500630074002000490044002C0020004E0061006D0065002000660072006F006D002000640062006F002E005B007B0030007D002E0054006F004C006900730074005D0028002900000F6300730063002E006500780065000009540045004D00500000035C0000072E006300730000092E0064006C006C000080912F006F007000740069006D0069007A0065002B0020002F006E006F006C006F0067006F0020002F00700072006500660065007200720065006400750069006C0061006E0067003A0065006E0020002F007400610072006700650074003A006C0069006200720061007200790020002F006F00750074003A0022007B0030007D002200200022007B0031007D0022002000002B43006F006D00700069006C006500720020006500780069007400200063006F006400650020003D002000000769006E007400000945006E0075006D0000032D00010369000083650D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D002E0044006100740061002E00530071006C00540079007000650073003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E00670020004D006900630072006F0073006F00660074002E00530071006C005300650072007600650072002E005300650072007600650072003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D002E0043006F006C006C0065006300740069006F006E0073003B000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007500730069006E0067002000530079007300740065006D002E0043006F006C006C0065006300740069006F006E0073002E00470065006E0065007200690063003B000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020006E0061006D006500730070006100630065002000530071006C004100750074006F0045006E0075006D007300470065006E0065007200610074006500640020000D000A002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020002000200020007B00200000357000750062006C0069006300200065006E0075006D0020007B0030007D0020003A0020007B0031007D0020007B007B002000200000177B0030007D0020003D0020007B0031007D002C002000000720007D002000007F7000750062006C0069006300200073007400610074006900630020007B0030007D0020007B0031007D0020007B007B00200067006500740020007B007B002000720065007400750072006E0020006E006500770020007B0030007D0028007B0032007D002E007B0031007D0029003B0020007D007D0020007D007D002000007B7000750062006C0069006300200073007400610074006900630020007B0030007D0020007B0031007D007B0032007D0020007B007B00200067006500740020007B007B002000720065007400750072006E00200028007B0030007D0029007B0033007D002E007B0032007D003B0020007D007D0020007D007D00008B710D000A0020002000200020005B00530065007200690061006C0069007A00610062006C0065005D000D000A0020002000200020005B00530071006C00550073006500720044006500660069006E00650064005400790070006500280046006F0072006D00610074002E00550073006500720044006500660069006E00650064002C0020004900730042007900740065004F0072006400650072006500640020003D00200074007200750065002C00200049007300460069007800650064004C0065006E0067007400680020003D00200074007200750065002C0020004D00610078004200790074006500530069007A0065003D00340029005D0020000D000A0020002000200020007000750062006C0069006300200073007400720075006300740020007B0030007D0020003A00200049004E0075006C006C00610062006C0065002C002000200020004900420069006E00610072007900530065007200690061006C0069007A0065000D000A0020002000200020007B007B000D000A00200020002000200020002000200020007000750062006C006900630020007B0030007D0028007B0031007D002000760061006C0029000D000A00200020002000200020002000200020007B007B000D000A002000200020002000200020002000200020002000200020005F00760061006C007500650020003D002000760061006C003B0020005F006E0075006C006C0020003D002000660061006C00730065003B000D000A00200020002000200020002000200020007D007D000D000A000D000A00200020002000200020002000200020007000750062006C006900630020007B0031007D003F002000560061006C00750065000D000A00200020002000200020002000200020007B007B000D000A0020002000200020002000200020002000200020002000200067006500740020007B007B002000720065007400750072006E0020005F00760061006C00750065003B0020007D007D000D000A0020002000200020002000200020002000200020002000200073006500740020007B007B0020005F00760061006C007500650020003D002000760061006C00750065003B0020005F006E0075006C006C0020003D0020002800760061006C007500650020003D003D0020006E0075006C006C0029003B0020007D007D0020000D000A00200020002000200020002000200020007D007D000D000A0020002000200020002000200020002000700072006900760061007400650020007B0031007D003F0020005F00760061006C00750065003B000D000A00200020002000200020002000200020007000720069007600610074006500200062006F006F006C0020005F006E0075006C006C003B000D000A00200020002000200020002000200020007000750062006C006900630020006F007600650072007200690064006500200073007400720069006E006700200054006F0053007400720069006E006700280029000D000A00200020002000200020002000200020007B007B000D000A00200020002000200020002000200020002000200020002000720065007400750072006E002000560061006C007500650020003D003D0020006E0075006C006C0020003F00200073007400720069006E0067002E0045006D0070007400790020003A00200045006E0075006D002E004700650074004E0061006D006500280074007900700065006F00660028007B0031007D0029002C002000560061006C007500650029003B000D000A00200020002000200020002000200020007D007D000D000A00200020002000200020002000200020007000750062006C0069006300200069006E0074003F00200054006F0049006E007400280029000D000A00200020002000200020002000200020007B007B000D000A00200020002000200020002000200020002000200020002000720065007400750072006E002000560061006C007500650020003D003D0020006E0075006C006C0020003F002000280069006E0074003F0029006E0075006C006C0020003A002000280069006E0074002900560061006C00750065002E00560061006C00750065003B000D000A00200020002000200020002000200020007D007D000D000A00200020000D000A000D000A000D000A000D000A00200020002000200020002000200020005B00530071006C00460075006E006300740069006F006E002800460069006C006C0052006F0077004D006500740068006F0064004E0061006D00650020003D002000220054006F004C00690073007400460069006C006C0052006F00770022002C0020005400610062006C00650044006500660069006E006900740069006F006E003D00220049004400200049004E0054002C0020004E0061006D00650020006E007600610072006300680061007200280034003000300030002900220029005D00200020000D000A00200020002000200020002000200020007000750062006C006900630020007300740061007400690063002000490045006E0075006D0065007200610062006C006500200054006F004C006900730074002800290020000D000A00200020002000200020002000200020007B007B0020000D000A0020002000200020002000200020002000200020002000200076006100720020006C007300740020003D0020006E006500770020004C006900730074003C004B0065007900560061006C007500650050006100690072003C0069006E0074002C00200073007400720069006E0067003E003E00280029003B0020000D000A0020002000200020002000200020002000200020002000200066006F00720065006100630068002000280076006100720020006400610074006100200069006E00200045006E0075006D002E00470065007400560061006C00750065007300280074007900700065006F00660028007B0031007D0029002900290020000D000A0020002000200020002000200020002000200020002000200020002000200020006C00730074002E0041006400640028006E006500770020004B0065007900560061006C007500650050006100690072003C0069006E0074002C00200073007400720069006E0067003E002800280069006E0074002900200064006100740061002C002000280028007B0031007D0029002000640061007400610029002E0054006F0053007400720069006E00670028002900290029003B0020000D000A00200020002000200020002000200020002000200020002000720065007400750072006E0020006C00730074003B0020000D000A00200020002000200020002000200020007D007D0020000D000A0020000D000A00200020002000200020002000200020007000750062006C00690063002000730074006100740069006300200076006F0069006400200054006F004C00690073007400460069006C006C0052006F00770028004F0062006A0065006300740020006F0062006A002C0020006F0075007400200069006E0074002000690064002C0020006F0075007400200073007400720069006E00670020006E0061006D006500290020000D000A00200020002000200020002000200020007B007B000D000A002000200020002000200020002000200020002000200020007600610072002000640061007400610020003D00200028004B0065007900560061006C007500650050006100690072003C0069006E0074002C00200073007400720069006E0067003E0029006F0062006A003B000D000A002000200020002000200020002000200020002000200020006900640020003D00200064006100740061002E004B00650079003B000D000A002000200020002000200020002000200020002000200020006E0061006D00650020003D00200064006100740061002E00560061006C00750065003B0020000D000A00200020002000200020002000200020007D007D0020000D000A000D000A000D000A000D000A000D000A000D000A000D000A000D000A000D000A00200020002000200020002000200020000088550D000A00200020002000200020002000200020007000750062006C00690063002000730074006100740069006300200062006F006F006C0020006F00700065007200610074006F00720020003D003D0028007B0030007D00200061002C0020007B0030007D0020006200290020007B007B002000720065007400750072006E00200061002E00560061006C007500650020003D003D00200062002E00560061006C00750065003B0020007D007D000D000A00200020002000200020002000200020007000750062006C00690063002000730074006100740069006300200062006F006F006C0020006F00700065007200610074006F007200200021003D0028007B0030007D00200061002C0020007B0030007D0020006200290020007B007B002000720065007400750072006E00200061002E00560061006C0075006500200021003D00200062002E00560061006C00750065003B0020007D007D000D000A00200020002000200020002000200020007000750062006C0069006300200062006F006F006C002000490073004E0075006C006C0020007B007B00200067006500740020007B007B002000720065007400750072006E0020005F006E0075006C006C003B0020007D007D0020007D007D000D000A00200020002000200020002000200020007000750062006C0069006300200073007400610074006900630020007B0030007D0020004E0075006C006C0020007B007B00200067006500740020007B007B0020007600610072002000680020003D0020006E006500770020007B0030007D0020007B007B0020005F006E0075006C006C0020003D002000740072007500650020007D007D003B002000720065007400750072006E00200068003B0020007D007D0020007D007D000D000A000D000A00200020002000200020002000200020005B00530071006C00460075006E006300740069006F006E00280049007300440065007400650072006D0069006E006900730074006900630020003D002000740072007500650029005D000D000A00200020002000200020002000200020007000750062006C0069006300200073007400610074006900630020007B0030007D002000500061007200730065002800530071006C0053007400720069006E0067002000730029000D000A00200020002000200020002000200020007B007B000D000A00200020002000200020002000200020002000200020002000690066002000280073002E00490073004E0075006C006C0029002000720065007400750072006E0020004E0075006C006C003B000D000A002000200020002000200020002000200020002000200020007600610072002000750020003D0020006E006500770020007B0030007D000D000A0020002000200020002000200020002000200020002000200020002000200020007B007B002000560061006C007500650020003D00200028007B0031007D00290045006E0075006D002E0050006100720073006500280074007900700065006F00660028007B0031007D0029002C00200073002E0054006F0053007400720069006E00670028002900290020007D007D003B000D000A00200020002000200020002000200020002000200020002000720065007400750072006E00200075003B000D000A00200020002000200020002000200020007D007D000D000A000D000A00200020002000200020002000200020007000750062006C0069006300200076006F00690064002000570072006900740065002800530079007300740065006D002E0049004F002E00420069006E0061007200790057007200690074006500720020007700290020000D000A00200020002000200020002000200020007B007B0020000D000A00200020002000200020002000200020002000200020002000690066002000280074006800690073002E00490073004E0075006C006C00290020000D000A002000200020002000200020002000200020002000200020007B007B0020000D000A00200020002000200020002000200020002000200020002000200020002000200077002E00570072006900740065002800280069006E00740029002D00310029003B0020000D000A002000200020002000200020002000200020002000200020002000200020002000720065007400750072006E003B0020000D000A002000200020002000200020002000200020002000200020007D007D0020000D000A0020002000200020002000200020002000200020002000200077002E00570072006900740065002800280069006E00740029005F00760061006C007500650029003B0020000D000A00200020002000200020002000200020007D007D0020000D000A00090009007000750062006C0069006300200076006F0069006400200052006500610064002800530079007300740065006D002E0049004F002E00420069006E0061007200790052006500610064006500720020007200290020000D000A00200020002000200020002000200020007B007B0020000D000A0020002000200020002000200020002000200020002000200069006E0074002000640061007400610020003D00200072002E00520065006100640049006E00740033003200280029003B0020000D000A002000200020002000200020002000200020002000200020006900660020002800640061007400610020003D003D0020002D0031002900200074006800690073002E005F006E0075006C006C0020003D00200074007200750065003B0020000D000A0009000900090065006C007300650020002000200074006800690073002E00560061006C007500650020003D00200028007B0031007D00290064006100740061003B0020000D000A00200020002000200020002000200020007D007D0020000D000A0020002000200020007D007D0020000D000A0001037D0000FF7FAD9F213D17428930FF44BBA271C50008B77A5C561934E089060001120D1111060001111511110E0005120D111111111111111111110C0003121C15121901121C0E0E0A0001120D15121901121C05000112140E050001121D0E080003011C1008100E0320000102060802060E0320000E042001010E0320000804200011180420001D050328000E04280011180428001D0505200101111803061118052001011D0503061D050420001D0E052001011D0E03061D0E0428001D0E04000000000401000000040200000004030000000B0615121901151121020E080920010E15121901121C12010001005408074D617853697A65FFFFFFFF05200101115904200101080600030E0E0E0E0420001D03052001011D0309070512140E0E1D0E0806000111151D050407011214808F010001005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A44617461416363657373010000000600020E0E1D1C062002010E126D05200101117D0520001280810615121901121C0420011C08042001080805200101130006151121020E0807200201130013010A15121901151121020E08032000021E070E126D0E127512808115121901121C0E0E0E08121C121C1275120D1D1C0920001511808D011300071511808D01121C0420001300050002020E0E0D0703121C121C1511808D01121C05070212200E060702121412148113010004005455794D6963726F736F66742E53716C5365727665722E5365727665722E446174614163636573734B696E642C2053797374656D2E446174612C2056657273696F6E3D342E302E302E302C2043756C747572653D6E65757472616C2C205075626C69634B6579546F6B656E3D623737613563353631393334653038390A4461746141636365737301000000540E044E616D652053716C4175746F456E756D732E456E756D4D656D626572735F43757272656E74540E1146696C6C526F774D6574686F644E616D6519456E756D4D656D6265727343757272656E7446696C6C526F77540E0F5461626C65446566696E6974696F6E1B494420494E542C204E616D65206E766172636861722834303030290500020E0E1C0615121901120C180709126D0E127512808115121901120C120C120C1275121D040701120C0300000E0500020E0E0E0400010E0E0500001180990500010E1D1C0607021D1C1D1C050002010E0E0520001280A90600030E0E1C1C04200101020520001280AD0500011D050E040001010E0A07051280A50E081D0E080520020E0E0E0507011180990620011280BD0E0820031280BD0E1C1C0B1511808D01151121020E0804200013010920041280BD0E1C1C1C0820021280BD0E1D1C3207081280BD121C151121020E08151121020E081511808D01121C1511808D01151121020E081511808D01151121020E081D1C1101000C53716C4175746F456E756D73000005010000000017010012436F7079726967687420C2A920203230313300000C010007312E302E302E3300000801000200000000000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301000000000000001EBDFF5100000000020000001C010000585B0000583D000052534453980985C78CD62441825051AFABB1A53201000000633A5C576F726B735C53514C4175746F456E756D735C53514C4175746F456E756D735C6F626A5C52656C656173655C53514C4175746F456E756D732E70646200000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000009C5C00000000000000000000BE5C0000002000000000000000000000000000000000000000000000B05C00000000000000000000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF250020001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058600000D00200000000000000000000D00234000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE00000100000001000300000000000100030000003F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B00430020000010053007400720069006E006700460069006C00650049006E0066006F0000000C020000010030003000300030003000340062003000000044000D000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C004100750074006F0045006E0075006D00730000000000300008000100460069006C006500560065007200730069006F006E000000000031002E0030002E0030002E003300000044001100010049006E007400650072006E0061006C004E0061006D0065000000530051004C004100750074006F0045006E0075006D0073002E0064006C006C00000000004800120001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020002000320030003100330000004C00110001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530051004C004100750074006F0045006E0075006D0073002E0064006C006C00000000003C000D000100500072006F0064007500630074004E0061006D00650000000000530071006C004100750074006F0045006E0075006D00730000000000340008000100500072006F006400750063007400560065007200730069006F006E00000031002E0030002E0030002E003300000038000800010041007300730065006D0062006C0079002000560065007200730069006F006E00000031002E0030002E0030002E0033000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000005000000C000000D03C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
WITH PERMISSION_SET = UNSAFE

GO

CREATE FUNCTION [dbo].[SqlAutoEnums.EnumMembers_Current](@enumName [nvarchar](4000)) RETURNS TABLE ( [ID] [int] NULL, [Name] [nvarchar](4000) NULL ) WITH EXECUTE AS N'dbo' AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[EnumMembersCurrent] 
GO 


CREATE FUNCTION [dbo].[SqlAutoEnumsCompile](@code [nvarchar](max)) RETURNS [varbinary](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsCompile] 
GO 



CREATE FUNCTION [dbo].[SqlAutoEnumsGenerate](@tableName [nvarchar](4000), @columnPrefix [nvarchar](4000), @columnName [nvarchar](4000), @columnMember [nvarchar](4000), @columnValue [nvarchar](4000)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsGenerate] 
GO 


CREATE FUNCTION [dbo].[SqlAutoEnumsTryCompile](@code [nvarchar](max)) RETURNS [nvarchar](max) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SQLAutoEnums].[UserDefinedFunctions].[SqlAutoEnumsTryCompile] 
GO 




2. Создаем тестовую таблицу SqlAutoEnums.Data, в которой у нас будет лежать описание перечислений и view, смотрящую на нее (позднее можно с сохранением структуры перенаправить ее на другую таблицу, где у вас хранятся перечисления, тогда таблица SqlAutoEnums.Data не нужна):
CREATE TABLE dbo.[SqlAutoEnums.Data]...

CREATE TABLE [dbo].[SqlAutoEnums.Data](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Prefix] [nvarchar](50) NOT NULL,
	[Name] [nvarchar](50) NOT NULL,
	[MemberName] [nvarchar](50) NOT NULL,
	[MemberValue] [int] NOT NULL,
 CONSTRAINT [PK_SqlAutoEnums.Data] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_SqlAutoEnums.Data] ON [dbo].[SqlAutoEnums.Data]
(
	[Prefix] ASC,
	[Name] ASC,
	[MemberName] ASC,
	[MemberValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE UNIQUE NONCLUSTERED INDEX [IX_SqlAutoEnums.Data.A] ON [dbo].[SqlAutoEnums.Data]
(
	[Name] ASC,
	[MemberValue] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE VIEW [dbo].[SqlAutoEnums.Data.View]
	AS 
SELECT  ID ,
        Prefix ,
        Name ,
        MemberName ,
        MemberValue 
FROM	dbo.[SqlAutoEnums.Data]



3. Кучка утилитарно-вспомогательных функций:
CREATE FUNCTION...


CREATE FUNCTION [dbo].[SqlAutoEnums.Enum_Equals]
(
	@enumName NVARCHAR(100)
)
RETURNS bit 
AS
BEGIN
	DECLARE @res BIT = 0;

	SET @res =	CASE
					WHEN	
							EXISTS	(
										SELECT Name AS MemberName, ID AS MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_Current](@enumName)
										except
										select MemberName, MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_New](@enumName)
									)
							OR
							EXISTS	(
										SELECT MemberName, MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_New](@enumName)
										EXCEPT
										SELECT Name AS MemberName, ID AS MemberValue FROM dbo.[SqlAutoEnums.EnumMembers_Current](@enumName)                                      
									)                          
						THEN 0
					ELSE 1
				END;

	RETURN @res;
END

GO
--=================================================================================================================

CREATE FUNCTION [dbo].[SqlAutoEnums.Enum_HasDependencies]
(
	@schemaName NVARCHAR(100),
	@typeName NVARCHAR(100),
	@onColumns BIT,
	@oncomputedColumns BIT,
	@onParameters BIT,
	@onCheckConstraints BIT,
	@onCode BIT
)
RETURNS bit 
AS
BEGIN
	DECLARE @res BIT = 0;
	DECLARE @typeidname NVARCHAR(255) = '[' + @schemaName + '].[' + @typeName + ']';

	IF (@onColumns = 1)
	BEGIN
		SET @res = CASE WHEN EXISTS	(
										SELECT	1 --OBJECT_NAME(object_id) AS object_name ,c.name AS column_name ,SCHEMA_NAME(t.schema_id) AS schema_name,TYPE_NAME(c.user_type_id) AS user_type_name,c.max_length,c.precision,c.scale,c.is_nullable,c.is_computed
										FROM	sys.columns AS c
										INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
										WHERE	c.user_type_id = TYPE_ID(@typeidname)
									) THEN 1
					END;
		IF (1 = @res) RETURN @res;
	END;

	IF (@oncomputedColumns = 1)
	BEGIN
		SET @res = CASE WHEN EXISTS	(
										SELECT	1 --OBJECT_NAME(object_id) AS OBJECT_NAME ,COL_NAME(object_id, column_id) AS column_name
										FROM	sys.sql_dependencies
										WHERE	referenced_major_id = TYPE_ID(@typeidname) AND
												class = 2 AND -- schema-bound references to type 
												OBJECTPROPERTY(object_id, 'IsTable') = 1
									) THEN 1
					END;
		IF (1 = @res) RETURN @res;
	END;


	IF (@onParameters = 1)
	BEGIN
		SET @res = CASE WHEN EXISTS	(
										SELECT	1 -- OBJECT_NAME(object_id) AS object_name ,NULL AS procedure_number ,name AS param_name ,parameter_id AS param_num ,TYPE_NAME(p.user_TYPE_ID) AS type_name
										FROM	sys.parameters AS p
										WHERE	p.user_TYPE_ID = TYPE_ID(@typeidname)
										UNION 
										SELECT	1 -- OBJECT_NAME(object_id) AS object_name ,procedure_number ,name AS param_name ,parameter_id AS param_num ,TYPE_NAME(p.user_TYPE_ID) AS type_name
										FROM	sys.numbered_procedure_parameters AS p
										WHERE	p.user_TYPE_ID = TYPE_ID(@typeidname)									
									) THEN 1
					END;
		IF (1 = @res) RETURN @res;
	END;


	IF (@onCheckConstraints = 1)
	BEGIN
		SET @res = CASE WHEN EXISTS	(
										SELECT 1 -- SCHEMA_NAME(o.schema_id) AS schema_name ,OBJECT_NAME(o.parent_object_id) AS table_name ,OBJECT_NAME(o.object_id) AS constraint_name
										FROM	sys.sql_dependencies AS d
										JOIN sys.objects AS o ON o.object_id = d.object_id
										WHERE	referenced_major_id = TYPE_ID(@typeidname) AND 
												class = 2 AND -- schema-bound references to type
												OBJECTPROPERTY(o.object_id, 'IsCheckCnst') = 1 -- exclude non-CHECK dependencies									
									) THEN 1
					END;
		IF (1 = @res) RETURN @res;
	END;



	IF (@onCode = 1)
	BEGIN
		SET @res = CASE WHEN EXISTS	(
										SELECT	1 -- SCHEMA_NAME(o.schema_id) AS dependent_object_schema ,OBJECT_NAME(o.object_id) AS dependent_object_name ,o.type_desc AS dependent_object_type ,d.class_desc AS kind_of_dependency ,TYPE_NAME (d.referenced_major_id) AS type_name
										FROM	sys.sql_dependencies AS d 
										JOIN sys.objects AS o ON d.object_id = o.object_id AND o.type IN ('FN','IF','TF', 'V', 'P')
										WHERE	d.class = 2 AND -- dependencies on types
												d.referenced_major_id = TYPE_ID(@typeidname)
									) THEN 1
					END;
		IF (1 = @res) RETURN @res;
	END;


	RETURN 0;
END
GO
--=================================================================================================================

CREATE FUNCTION [dbo].[SqlAutoEnums.EnumMembers_New]
(	
	@enumName NVARCHAR(100)
)
RETURNS TABLE 
AS
RETURN 
(
	-- select * from [SqlAutoEnums.NewEnumVals]()

	SELECT	Prefix+Name AS EnumName, 
			MemberName AS MemberName, 
			MemberValue AS MemberValue 
	FROM	dbo.[SqlAutoEnums.Data.View]
	WHERE	Prefix+Name = @enumName
)
GO
--=================================================================================================================

CREATE FUNCTION [dbo].[SqlAutoEnums.Enums_Current]()
RETURNS TABLE 
AS
RETURN
(
		SELECT      atold.assembly_id AS AssemblyId,
					asm.name AS AssemblyName,
					atold.user_type_id AS EnumId,
					atold.name AS EnumName
		FROM        sys.assembly_types atold
		INNER JOIN sys.assemblies asm on asm.name LIKE 'SQLAutoEnums.Generated%' AND atold.assembly_id = asm.assembly_id
)
GO
--======================================================================================================================

CREATE FUNCTION [dbo].[SqlAutoEnums.Enums_New]()
RETURNS TABLE 
AS
RETURN
(
	SELECT	DISTINCT Prefix+Name AS EnumName
	FROM	dbo.[SqlAutoEnums.Data.View]
)
GO
--=================================================================================================================

CREATE FUNCTION [dbo].[SqlAutoEnums.EnumsMembers_Current]
(	
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT	e.AssemblyId,	e.AssemblyName,	e.EnumId,	e.EnumName,
			v.Name AS MemberName, v.ID AS MemberValue
	FROM	(
				SELECT	AssemblyId,	AssemblyName,	EnumId,	CAST(EnumName AS NVARCHAR(100)) AS EnumName
				FROM	dbo.[SqlAutoEnums.Enums_Current]()
			) e
	CROSS APPLY dbo.[SqlAutoEnums.EnumMembers_Current](e.EnumName) AS v
)
GO
--=================================================================================================================

CREATE FUNCTION [dbo].[SqlAutoEnums.EnumsMembers_New]
(	
)
RETURNS TABLE 
AS
RETURN 
(
	SELECT	Prefix+Name AS EnumName, 
			MemberName AS MemberName, 
			MemberValue AS MemberValue 
	FROM	dbo.[SqlAutoEnums.Data.View]
)
GO




4. Делаем процедуру для создания/обновления перечислений
CREATE PROCEDURE dbo.[SqlAutoEnums.Renew]...



CREATE PROCEDURE [dbo].[SqlAutoEnums.Renew]
WITH EXECUTE AS SELF
AS
BEGIN

DECLARE @msg NVARCHAR(MAX);
BEGIN TRY
	BEGIN TRAN 
        --============================================================================================================
	-- dropping current enums
	PRINT 'Current enums: clearing...';
	IF (EXISTS	( 
					SELECT	1 
					FROM	dbo.[SqlAutoEnums.Enums_Current]() ec 
					WHERE	dbo.[SqlAutoEnums.Enum_Equals](ec.EnumName) = 0 AND 
							dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 1
				))
	BEGIN
		SET @msg = 'Cannot modify or drop enums cause of dependencies: ';
		SELECT	@msg += ec.EnumName + ', '
		FROM	dbo.[SqlAutoEnums.Enums_Current]() ec 
		WHERE	dbo.[SqlAutoEnums.Enum_Equals](ec.EnumName) = 0 AND 
				dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 1
		SET @msg = SUBSTRING(@msg, 1, LEN(@msg)-2);
		RAISERROR(@msg, 16, 2);			
	END;

	--assembly list to drop
	DECLARE @asstodrop TABLE (Name NVARCHAR(MAX));
	INSERT INTO @asstodrop (Name)
	SELECT	ec.AssemblyName
	FROM	dbo.[SqlAutoEnums.Enums_Current]() ec 
	GROUP BY ec.AssemblyName
	HAVING SUM(CAST(dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) AS INT)) = 0


	-- dropping enums 
	DECLARE @qryDropEnum NVARCHAR(MAX);
	DECLARE @qryDropEnumToList NVARCHAR(MAX);
	DECLARE @oldEnumName NVARCHAR(MAX);
	DECLARE enumCursor CURSOR FOR 
		SELECT	ec.EnumName
		FROM	dbo.[SqlAutoEnums.Enums_Current]() ec 
		WHERE	dbo.[SqlAutoEnums.Enum_HasDependencies]('', ec.EnumName, 1, 1, 1, 1, 1) = 0
	OPEN enumCursor;
	FETCH NEXT FROM enumCursor INTO @oldEnumName;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT '    Dropping enum '  + @oldEnumName;
		SET @qryDropEnum = 'DROP TYPE [dbo].[' + @oldEnumName + ']';
		SET @qryDropEnumToList = 'DROP FUNCTION [dbo].[' + @oldEnumName + '.ToList]';  
    
		IF (EXISTS (SELECT 1 FROM Information_schema.Routines WHERE	SPECIFIC_SCHEMA = 'dbo' AND SPECIFIC_NAME = @oldEnumName + '.ToList'))
			EXEC sp_executesql @qryDropEnumToList;
		EXEC sp_executesql @qryDropEnum;

		FETCH NEXT FROM enumCursor INTO @oldEnumName;
	END;
	CLOSE enumCursor;
	DEALLOCATE enumCursor;

	-- dropping assemblies 
	DECLARE @qryDropAss NVARCHAR(MAX);
	DECLARE @oldAssName NVARCHAR(MAX);
	DECLARE assCursor CURSOR FOR  SELECT Name FROM @asstodrop;
	OPEN assCursor;
	FETCH NEXT FROM assCursor INTO @oldAssName;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT '    Dropping assembly '  + @oldAssName;
		SET @qryDropAss = 'DROP ASSEMBLY [' + @oldAssName + ']'
		EXEC sp_executesql @qryDropAss;

		FETCH NEXT FROM assCursor INTO @oldAssName;
	END;
	CLOSE assCursor;
	DEALLOCATE assCursor;

	PRINT 'Current enums: clear.';

        --============================================================================================================
	-- creating new assembly
	PRINT 'New assembly: generating...';
	DECLARE @newAsmName NVARCHAR(255) = 'SQLAutoEnums.Generated.' + CAST(NEWID() AS NVARCHAR(100));
	DECLARE @newAsmId BIGINT;
	PRINT '    Generated assembly name = ' + @newAsmName;
	DECLARE @code NVARCHAR(MAX) =  dbo.SqlAutoEnumsGenerate('[SqlAutoEnums.Data.View]', 'Prefix', 'Name', 'MemberName', 'MemberValue');
	DECLARE @compilemsg NVARCHAR(MAX) = dbo.SqlAutoEnumsTryCompile(@code);
	DECLARE @bin VARBINARY(MAX) =  dbo.SqlAutoEnumsCompile(@code);
	IF (@bin IS NULL) 
	BEGIN
		SET @msg = 'Cannot compile generated code:' + @compilemsg;
		RAISERROR(@msg, 16, 2);	  
	END;
	DECLARE @qryCreateAssembly NVARCHAR(MAX) =  'CREATE ASSEMBLY [' + @newAsmName + '] FROM ' + master.dbo.fn_varbintohexstr(@bin) + ' WITH PERMISSION_SET = SAFE;';
	EXEC sp_executesql @qryCreateAssembly;
	SELECT @newAsmId = asm.assembly_id FROM sys.assemblies asm WHERE asm.name = @newAsmName;
	PRINT 'New assembly: done.';

        --===========================================================================================================
	--  registering new enums 
	PRINT 'New enums: registering...'
	DECLARE @qryCreateEnum NVARCHAR(MAX);
	DECLARE @qryCreateEnumToList NVARCHAR(MAX);
	DECLARE @newEnumName NVARCHAR(MAX);
	DECLARE newEnumCursor CURSOR FOR 
		SELECT	EnumName
		FROM	dbo.[SqlAutoEnums.Enums_New]() en
		WHERE	en.EnumName NOT IN (SELECT EnumName FROM dbo.[SqlAutoEnums.Enums_Current]());
	OPEN newEnumCursor;
	FETCH NEXT FROM newEnumCursor INTO @newEnumName;
	WHILE @@FETCH_STATUS = 0
	BEGIN
		PRINT '    Registering enum '  + @newEnumName;
		
		SET @qryCreateEnum = 'CREATE TYPE [dbo].[' + @newEnumName + '] EXTERNAL NAME [' + @newAsmName + '].[SqlAutoEnumsGenerated.' + @newEnumName + ']';
		EXEC sp_executesql @qryCreateEnum;

		SET @qryCreateEnumToList = 'CREATE FUNCTION [' + @newEnumName + '.ToList]() RETURNS TABLE (ID INT, Name NVARCHAR(4000)) EXTERNAL NAME [' + @newAsmName + '].[SqlAutoEnumsGenerated.' + @newEnumName + '].ToList;';
		EXEC sp_executesql @qryCreateEnumToList;

		FETCH NEXT FROM newEnumCursor INTO @newEnumName;
	END;
	CLOSE newEnumCursor;
	DEALLOCATE newEnumCursor;
	PRINT 'New enums: done.'

	COMMIT TRANSACTION;
END TRY
BEGIN CATCH
	IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION;
	THROW;
END CATCH;    

END
GO



4. На таблицу вешаем триггер для автоматического обновления перечислений
CREATE TRIGGER dbo.[SqlAutoEnums.Renew.Trigger]...

CREATE TRIGGER dbo.[SqlAutoEnums.Renew.Trigger]
   ON  [dbo].[SqlAutoEnums.Data]
   AFTER INSERT, DELETE, UPDATE
AS 
BEGIN
	EXEC dbo.[SqlAutoEnums.Renew];
END


Что теперь с этим делать?



Сгенерируем тестовые перечисления

INSERT INTO dbo.[SqlAutoEnums.Data]...
INSERT INTO dbo.[SqlAutoEnums.Data] (Prefix, Name, MemberName, MemberValue)
VALUES  ('Enum', 	'Lolly',	'C',	14 ),
		('Enum', 	'Lolly',	'A',	1  ),
		('Enum', 	'Lolly',	'B',	2  ),
		('Enum', 	'Process',  'Running',	  1  ),
		('Enum', 	'Process',  'Suspended',  2  ),
		('Enum', 	'Process',  'Terminated', 3  )


Посмотрим, что у нас теперь есть:


-- перечисления
SELECT * FROM [SqlAutoEnums.Enums_Current]()
-- перечисления с членами
SELECT * FROM [SqlAutoEnums.EnumsMembers_Current]()
-- члены перечисления EnumProcess
SELECT * FROM [EnumProcess.ToList]()

--используем переменные
DECLARE @processState EnumProcess;
SET @processState = EnumProcess::Running;
PRINT @processState.ToString();

--поля в таблицах
DECLARE @process TABLE (ID INT, Comment NVARCHAR(100), ProcessState EnumProcess);
INSERT INTO @process (ID, Comment, ProcessState)
VALUES  (0, 'прямое присваивание: EnumProcess::Suspended', EnumProcess::Suspended),
		(0, 'из строки: "Running" ', EnumProcess::Parse('Running')),
		(0, 'из целого: 3', EnumProcess::Parse(3))

-- where
SELECT	ID, Comment, ProcessState, ProcessState.ToInt(), ProcessState.ToString()          
FROM	@process
WHERE	ProcessState = EnumProcess::Suspended OR
		ProcessState = @processState;

-- group
SELECT	ProcessState, ProcessState.ToInt(), ProcessState.ToString(), COUNT(*) AS [Count]
FROM	@process
GROUP BY ProcessState;



Немного о внутренностях



Сборка SqlAutoEnums занимается, собственно, тем, что из данных в таблице генерирует и регистрирует сборки SqlAutoEnums.Generated.*, в которых и находятся перечисления. Ввиду ограничений CLR Hosted Enviroment (тынц, тынц), генерирует топорно, string.Format и поскакали, компилирует вызовом csc.exe по пути Environment.GetEnvironmentVariable(«windir») + "\\Microsoft.NET\\Framework\\v3.5\\csc.exe" (убедитесь, что на сервере установлен .NET 3.5 пути установки используемой версии.NET. Убедитесь, что туда и к Environment.GetEnvironmentVariable(«TEMP»)) есть доступ. Однажды заменим что-нибудь менее топорное.

Ввиду того, что SQLServer не знает и не понимает enum, формируются структуры с полями по имени членов перечислений:

public enum FooEnum{A = 1, B = 2}
public struct MyFooEnum
{
     public MyFooEnum(FooEnum value)  { _value = value; }

     public static MyFooEnum A { get { return new MyFooEnum(FooEnum.A);} }
     public static MyFooEnum B { get { return new MyFooEnum(FooEnum.B);} }

     private FooEnum _value;
}

Вокруг наверчено еще много всякой технической обертки (подробнее о создании CLR типов можно почитать, например, здесь, MSDN, Google…

Следует помнить, что, как только вы использовали перечисление в работе (поля и вычисляемые поля в таблице, параметры процедур/функций и т.д.), то, как и любой другой пользовательский тип данных, изменить (ввиду отсутствия ALTER TYPE — делается DROP, затем CREATE) или удалить не получится, сначала надо убрать все ссылки на него. Впрочем, если тип используется только внутри кода процедур/функций, то можно, но, понятно, процедура/функция потом может вывалится с исключением, если не найдется перечисления или его части.
Поэтому при генерации новой сборки все перечисления, которые не имеют зависимостей, переносятся в нее, в «старой» сборке остаются перечисления, которые не нужно изменять и которые имеют зависимости.

Обещанные исходники сборки SqlAutoEnums.dll

Спасибо за внимание, надеюсь, кому-то пригодится вышеописанное или натолкнет на более продвинутый вариант.
Теги:
Хабы:
+8
Комментарии 7
Комментарии Комментарии 7

Публикации

Истории

Ближайшие события

Московский туристический хакатон
Дата 23 марта – 7 апреля
Место
Москва Онлайн
Геймтон «DatsEdenSpace» от DatsTeam
Дата 5 – 6 апреля
Время 17:00 – 20:00
Место
Онлайн