SQL Server ->> 重新创建Assembly和自动重建相关的数据库编程对象（存储过程，函数和触发器）

在SQL Server中，一旦一个Assembly被其他的数据库编程对象（存储过程，函数和触发器）引用了，这个Assembly就不能被删除。但是问题是，在SQL Server要更新一个Assembly的方法是先删除这个Assembly再用心的DLL地址或者编译后的二进制代码重新创建。这样就造成了，每次我们都要根据它的错误提示把那些引用到该Assembly的数据库编程对象的创建脚本导出后再删除这些编程对象，然后才可能删除Assembly。导出脚本的方法需要手动去利用SSMS这个IDE里面的GENERATE SCRIPT的功能来做。挺麻烦的。下面这个存储过程就是为了来解决这个问题。它会自动来干这个事情。

但是，它只是个半成品。为什么这么说呢？因为这个存储过程到目前为止还没办法支持Trigger的脚本导出。因为工作需要，而项目中只是用到CLR存储过程和函数，就没有继续完善Trigger的部分。今后再完善吧。

IF EXISTS(SELECT * FROM sys.procedures WHERE name = 'usp_RecreateAssemblyAndInvolvedSQLObjects' AND [schema_id] = SCHEMA_ID('dbo'))
    DROP PROCEDURE [dbo].[usp_RecreateAssemblyAndInvolvedSQLObjects]
GO


CREATE PROCEDURE [dbo].[usp_RecreateAssemblyAndInvolvedSQLObjects]
    @assembly_name SYSNAME,
    @new_binary_string NVARCHAR(MAX),
    @permission_set NVARCHAR(50) = NULL,
    @not_exists_create BIT = 0,
    @only_output_command BIT = 0
AS
BEGIN

SET NOCOUNT ON

IF OBJECT_ID('tempdb..#t') IS NOT NULL
    DROP TABLE #t

CREATE TABLE #t(id INT IDENTITY(1,1), drop_sqlobject_cmd NVARCHAR(MAX), create_sqlobject_cmd NVARCHAR(MAX))

IF OBJECT_ID('tempdb..#cmd') IS NOT NULL
    DROP TABLE #cmd

CREATE TABLE #cmd(id INT IDENTITY(1,1), cmd NVARCHAR(MAX))

DECLARE @cmd AS NVARCHAR(MAX)
DECLARE @msg NVARCHAR(4000)

IF NOT EXISTS(SELECT * FROM sys.assemblies WHERE name = @assembly_name)
BEGIN
    IF @not_exists_create = 1 
    BEGIN
        IF @permission_set NOT IN ('SAFE','EXTERNAL_ACCESS','UNSAFE')
        BEGIN
            RAISERROR('PERMISSION_SET in "CREATE ASSEMBLY" command should only be one of values: SAFE, EXTERNAL_ACCESS, UNSAFE.',16,1)
            RETURN
        END

        SET @cmd = '/**************************** recreate assembly ' + QUOTENAME(@assembly_name) + ' with the new binary string ****************************/' + REPLICATE(CHAR(13) + CHAR(10),2)

        INSERT #cmd VALUES(@cmd)

        SET @cmd = 'CREATE ASSEMBLY ' + QUOTENAME(@assembly_name) + CHAR(13) + CHAR(10) +
                    'FROM ' + CAST(@new_binary_string AS NVARCHAR(MAX)) + CHAR(13) + CHAR(10) +
                    'WITH PERMISSION_SET = ' + @permission_set + CHAR(13) + CHAR(10) + ';' + CHAR(13) + CHAR(10)

        INSERT #cmd VALUES(@cmd)

        GOTO CombineCmdString
    END
    ELSE
    BEGIN
        SET @msg = 'Assembly "' + @assembly_name + '" doesn''t exist in the database'
        RAISERROR(@msg,16,1)
        RETURN
    END
END

INSERT #t(drop_sqlobject_cmd, create_sqlobject_cmd)
SELECT 'DROP ' + CASE obj.type_desc WHEN 'CLR_STORED_PROCEDURE' THEN ' PROCEDURE '
                                    WHEN 'CLR_SCALAR_FUNCTION' THEN ' FUNCTION '
                                    WHEN 'CLR_TABLE_VALUED_FUNCTION' THEN ' FUNCTION '
                                    WHEN 'CLR_TRIGGER' THEN 'TRIGGER' 
                                    ELSE '' END + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name) 
               + CASE obj.type_desc WHEN 'CLR_TRIGGER' THEN ' ON ' + QUOTENAME(prn_sch.name) + '.' + QUOTENAME(prn_obj.name) 
                                    ELSE '' END  
               + ';' + CHAR(13) + CHAR(10) AS drop_sqlobject_cmd,
        'CREATE' + CASE obj.type_desc  WHEN 'CLR_STORED_PROCEDURE' THEN ' PROCEDURE '
                                        WHEN 'CLR_SCALAR_FUNCTION' THEN ' FUNCTION '
                                        WHEN 'CLR_TABLE_VALUED_FUNCTION' THEN ' FUNCTION '
                                        WHEN 'CLR_TRIGGER' THEN 'TRIGGER' 
                                        ELSE '' END + QUOTENAME(sch.name) + '.' + QUOTENAME(obj.name) 
                  + par.param_list
                  + CHAR(13) + CHAR(10) + CASE WHEN ass_mod.execute_as_principal_id IS NULL THEN 'WITH EXECUTE AS CALLER ' 
                                               WHEN ass_mod.execute_as_principal_id = -2 THEN 'WITH EXECUTE AS OWNER '
                                               ELSE 'WITH EXECUTE AS ''' + dp.name + '''' END
                  + CHAR(13) + CHAR(10) + 'AS EXTERNAL NAME '
                  + QUOTENAME(ass.name) + '.' + QUOTENAME(ass_mod.assembly_class) + '.' + QUOTENAME(ass_mod.assembly_method) 
                  + ';' + CHAR(13) + CHAR(10) AS create_sqlobject_cmd
FROM sys.assembly_modules ass_mod JOIN
        sys.objects obj ON ass_mod.object_id = obj.object_id JOIN
        sys.schemas sch ON sch.schema_id = obj.schema_id JOIN
        sys.assembly_files ass_f ON ass_f.assembly_id = ass_mod.assembly_id JOIN
        sys.assemblies ass ON ass.assembly_id = ass_f.assembly_id LEFT JOIN
        sys.objects prn_obj ON prn_obj.object_id = obj.parent_object_id LEFT JOIN
        sys.schemas prn_sch ON prn_sch.schema_id = prn_obj.schema_id LEFT JOIN
        sys.database_principals dp ON dp.principal_id = ass_mod.execute_as_principal_id CROSS APPLY
        (    SELECT IIF(obj.type_desc IN ('CLR_SCALAR_FUNCTION'),CHAR(13) + CHAR(10) + '(','') + STUFF(( 
            SELECT  CASE 
                        WHEN obj.type_desc IN ('CLR_STORED_PROCEDURE','CLR_TRIGGER') THEN 
                            ',' + CHAR(13) + CHAR(10) + par.name + SPACE(1) + UPPER(tp.name) + 
                            CASE WHEN tp.name IN ('nchar','nvarchar') THEN '(' + IIF(par.max_length = -1, 'MAX',CAST(par.max_length/2 AS NVARCHAR(10))) + ')'
                                 WHEN tp.name IN ('datetime2','datetimeoffset','time') THEN '(' + CAST(tp.scale AS NVARCHAR(10)) + ')'  
                                 WHEN tp.name IN ('binary','char','varbinary','varchar') THEN CAST(par.max_length AS NVARCHAR(10))
                                 WHEN tp.name IN ('decimal','numeric') THEN '(' + CAST(tp.precision AS NVARCHAR(10)) + ',' + CAST(tp.scale AS NVARCHAR(10)) + ')'
                                 ELSE '' END 
                            + IIF(par.is_output <> 0, ' OUTPUT ', '' )
                            + IIF(par.is_readonly <> 0, ' READONLY ', '')
                            + IIF(par.has_default_value <> 0, ' = ''' + CAST(par.default_value AS NVARCHAR(MAX)) + '''','')
                        WHEN obj.type_desc IN ('CLR_SCALAR_FUNCTION') THEN
                            CASE WHEN par.is_output <> 0 THEN 
                                    CHAR(13) + CHAR(10) + ') RETURNS ' + QUOTENAME(UPPER(tp.name)) ELSE 
                                    ',' + CHAR(13) + CHAR(10) + par.name + SPACE(1) + UPPER(tp.name) + 
                                    CASE WHEN tp.name IN ('nchar','nvarchar') THEN '(' + IIF(tp.max_length = -1, 'MAX',CAST(tp.max_length/2 AS NVARCHAR(10))) + ')'
                                         WHEN tp.name IN ('datetime2','datetimeoffset','time') THEN '(' + CAST(tp.scale AS NVARCHAR(10)) + ')'  
                                         WHEN tp.name IN ('binary','char','varbinary','varchar') THEN CAST(tp.max_length AS NVARCHAR(10))
                                         WHEN tp.name IN ('decimal','numeric') THEN '(' + CAST(tp.precision AS NVARCHAR(10)) + ',' + CAST(tp.scale AS NVARCHAR(10)) + ')'
                                         ELSE '' END
                                    + IIF(par.is_readonly <> 0, ' READONLY ', '')
                                    + IIF(par.has_default_value <> 0, ' = ''' + CAST(par.default_value AS NVARCHAR(MAX)) + '''','')
                             END
                        ELSE '' END
            FROM sys.parameters par JOIN
                    sys.types tp ON tp.system_type_id = par.system_type_id
            WHERE tp.name <> 'sysname' AND par.object_id = obj.object_id
            ORDER BY CASE WHEN obj.type_desc = 'CLR_SCALAR_FUNCTION' AND par.is_output = 1 THEN 1 ELSE 0 END,
                     par.parameter_id  
            FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'),1,2,'') AS param_list) as par
WHERE ass.name = @assembly_name;

SET @cmd = '/**************************** Drop CLR sql obbjects that reference assembly ' + QUOTENAME(@assembly_name) + ' ****************************/' + REPLICATE(CHAR(13) + CHAR(10),2)

INSERT #cmd VALUES(@cmd)

SET @cmd = (SELECT drop_sqlobject_cmd + CHAR(13) + CHAR(10) AS drop_sqlobject_cmd FROM #t ORDER BY id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)')

INSERT #cmd VALUES(@cmd)

SET @cmd = '/**************************** Drop assembly ' + QUOTENAME(@assembly_name) + ' ****************************/' + REPLICATE(CHAR(13) + CHAR(10),2)

INSERT #cmd VALUES(@cmd)

SET @cmd = 'DROP ASSEMBLY ' + QUOTENAME(@assembly_name) + ';' + CHAR(13) + CHAR(10) 

INSERT #cmd VALUES(@cmd)

SET @cmd = '/**************************** recreate assembly ' + QUOTENAME(@assembly_name) + ' with the new binary string ****************************/' + REPLICATE(CHAR(13) + CHAR(10),2)

INSERT #cmd VALUES(@cmd)

SET @cmd = (SELECT 'CREATE ASSEMBLY ' + QUOTENAME(name) + CHAR(13) + CHAR(10) +
            'FROM ' + CAST(@new_binary_string AS NVARCHAR(MAX)) + CHAR(13) + CHAR(10) +
            'WITH PERMISSION_SET = ' + IIF(@permission_set IS NULL, CASE permission_set WHEN 1 THEN 'SAFE' WHEN 2 THEN 'EXTERNAL_ACCESS' WHEN 3 THEN 'UNSAFE' END, @permission_set)  + ';' + CHAR(13) + CHAR(10)

FROM sys.assemblies
WHERE name = @assembly_name)

INSERT #cmd VALUES(@cmd)

SET @cmd = '/**************************** recreate CLR sql obbjects that reference assembly ' + QUOTENAME(@assembly_name) + ' ****************************/' + REPLICATE(CHAR(13) + CHAR(10),2)

INSERT #cmd VALUES(@cmd)

IF @only_output_command = 1
BEGIN
    SET @cmd = (SELECT create_sqlobject_cmd + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) AS create_sqlobject_cmd FROM #t ORDER BY id FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)') 

    INSERT #cmd VALUES(@cmd)
END
ELSE
BEGIN
    INSERT #cmd 
    SELECT create_sqlobject_cmd + CHAR(13) + CHAR(10) AS create_sqlobject_cmd FROM #t ORDER BY id
END

CombineCmdString:
IF @only_output_command = 1 
BEGIN
    SET @cmd = (SELECT (SELECT cmd + 'GO' + CHAR(13) + CHAR(10) FROM #cmd ORDER BY ID FOR XML PATH(''), TYPE).value('.', 'nvarchar(max)'))
END

IF @only_output_command = 1 
    SELECT @cmd AS cmd
ELSE
    BEGIN TRY

    BEGIN TRAN 

    DECLARE cur CURSOR FOR
    SELECT cmd FROM #cmd ORDER BY id

    OPEN cur

    FETCH NEXT FROM cur INTO @cmd

    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXEC(@cmd)

        FETCH NEXT FROM cur INTO @cmd
    END

    CLOSE cur
    DEALLOCATE cur

    COMMIT TRAN

    END TRY
    BEGIN CATCH
        IF CURSOR_STATUS('GLOBAL','cur') > -1
            CLOSE cur
        IF CURSOR_STATUS('GLOBAL','cur') = -1
            DEALLOCATE cur
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN

        DECLARE @err_msg AS NVARCHAR(4000)
        SET @err_msg = ERROR_MESSAGE();

        RAISERROR(@err_msg,16,1)
    END CATCH
END

GO