SQL Server ->> 重新创建Assembly和自动重建相关的数据库编程对象（存储过程，函数和触发器）
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQL Server ->> 重新创建Assembly和自动重建相关的数据库编程对象（存储过程，函数和触发器），小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6820字，纯文字阅读大概需要10分钟。
内容图文
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
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) + ‘;‘ + CHAR(13) + CHAR(10) +
‘GO‘
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) + ‘GO‘ + 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) + ‘GO‘ + 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 STUFF((
SELECT ‘,‘ + 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_output <> 0, ‘ OUTPUT ‘, ‘‘ )
+ IIF(par.is_readonly <> 0, ‘ READONLY ‘, ‘‘)
+ IIF(par.has_default_value <> 0, ‘ = ‘‘‘ + CAST(par.default_value AS NVARCHAR(MAX)) + ‘‘‘‘,‘‘)
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 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) + ‘GO‘ + 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) +
‘GO‘ + 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)
SET @cmd = (SELECT create_sqlobject_cmd + CHAR(13) + CHAR(10) AS create_sqlobject_cmd FROM #t ORDER BY id FOR XML PATH(‘‘), TYPE).value(‘.‘, ‘nvarchar(max)‘)
INSERT #cmd VALUES(@cmd)
CombineCmdString:
SET @cmd = (SELECT (SELECT cmd FROM #cmd ORDER BY ID FOR XML PATH(‘‘), TYPE).value(‘.‘, ‘nvarchar(max)‘))
EXEC (@cmd)
END
GO
SQL Server ->> 重新创建Assembly和自动重建相关的数据库编程对象（存储过程，函数和触发器）
标签：
本文系统来源：http://www.cnblogs.com/jenrrychen/p/4968806.html
内容总结
以上是互联网集市为您收集整理的SQL Server ->> 重新创建Assembly和自动重建相关的数据库编程对象（存储过程，函数和触发器）全部内容，希望文章能够帮你解决SQL Server ->> 重新创建Assembly和自动重建相关的数据库编程对象（存储过程，函数和触发器）所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】