MS SQL Serverの「実際の」列挙

 CREATE ASSEMBLY [SQLAutoEnums] FROM 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 

 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] 

 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 

 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 

 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] (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 )