Created
October 4, 2017 15:01
-
-
Save kenny-evitt/36418cb541ccc746f06603e036471cf4 to your computer and use it in GitHub Desktop.
SQL Server T-SQL script to drop-and-create a stored procedure to drop a database CLR assembly and all of its dependents, including other assemblies
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
IF EXISTS ( SELECT * | |
FROM sys.objects | |
WHERE object_id = OBJECT_ID(N'dbo.DropSqlServerAssemblyAndDependents') | |
AND type IN ( N'P', N'PC' ) ) | |
DROP PROCEDURE dbo.DropSqlServerAssemblyAndDependents; | |
GO | |
CREATE PROCEDURE dbo.DropSqlServerAssemblyAndDependents | |
@AssemblyName sysname | |
AS | |
-- Drop procedures and functions that reference this assembly | |
DECLARE assemblyProcedureFunctionReferencesCursor CURSOR LOCAL | |
FORWARD_ONLY | |
STATIC | |
READ_ONLY | |
FOR SELECT SchemaName = s.[name], ObjectName = o.[name], ObjectType = o.[type] | |
FROM sys.assembly_modules am | |
JOIN sys.assemblies a ON am.assembly_id = a.assembly_id | |
JOIN sys.objects o ON am.[object_id] = o.[object_id] | |
JOIN sys.schemas s ON o.[schema_id] = s.[schema_id] | |
WHERE a.[name] = @AssemblyName; | |
OPEN assemblyProcedureFunctionReferencesCursor; | |
DECLARE @schemaName sysname, | |
@objectName sysname, | |
@objectType char(2); | |
FETCH NEXT FROM assemblyProcedureFunctionReferencesCursor | |
INTO @schemaName, @objectName, @objectType; | |
DECLARE @statement nvarchar(MAX); | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
SET @statement = N'DROP' | |
+ N' ' | |
+ CASE | |
WHEN @objectType = 'PC' THEN N'PROCEDURE' | |
WHEN @objectType IN ( 'FS', 'FT' ) THEN N'FUNCTION' | |
END | |
+ N' ' | |
+ N'[' + @schemaName + N']' | |
+ N'.' | |
+ N'[' + @objectName + N']' | |
+ N';' | |
; | |
EXEC sp_executesql @statement = @statement; | |
FETCH NEXT FROM assemblyProcedureFunctionReferencesCursor | |
INTO @schemaName, @objectName, @objectType; | |
END | |
-- Drop assemblies that reference this one | |
DECLARE assemblyAssemblyReferencesCursor CURSOR LOCAL | |
FORWARD_ONLY | |
STATIC | |
READ_ONLY | |
FOR SELECT AssemblyName = a.[name] | |
FROM sys.assembly_references ar | |
JOIN sys.assemblies a ON ar.assembly_id = a.assembly_id | |
JOIN sys.assemblies a_referenced ON ar.referenced_assembly_id = a_referenced.assembly_id | |
WHERE a_referenced.[name] = @AssemblyName; | |
OPEN assemblyAssemblyReferencesCursor; | |
DECLARE @referencingAssemblyName sysname; | |
FETCH NEXT FROM assemblyAssemblyReferencesCursor | |
INTO @referencingAssemblyName; | |
WHILE @@FETCH_STATUS = 0 | |
BEGIN | |
IF EXISTS ( SELECT * FROM sys.assemblies WHERE [name] = @referencingAssemblyName ) | |
EXEC dbo.DropSqlServerAssemblyAndDependents | |
@AssemblyName = @referencingAssemblyName; | |
FETCH NEXT FROM assemblyAssemblyReferencesCursor | |
INTO @referencingAssemblyName; | |
END | |
-- Drop the assembly | |
SET @statement = N'DROP ASSEMBLY' | |
+ N' ' | |
+ N'[' + @AssemblyName + N']' | |
+ N';' | |
; | |
EXEC sp_executesql @statement = @statement; | |
GO |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment