we have database with some table-valued functions writen in C#. I have database project in visual studio, where I use "Deploy" action from Solution Explorer. However, when I want "alter" assembly and functions on testing database server from my developing database server I must manually (now semi-scripted) drop functions and assembly, then create assembly and create functions.
In 99% cases we don't change interface (function names, column names, column types, etc...).
Is there some easy way how to alter assembly and related functions/procedures?
The short answer seems to be that there is no other way.
Dropping and re-creating the assembly and linked functions is the only way for the database engine to validate that your interfaces are valid (and validly referenced).
Metadata about the relationship between assemblies and referencing functions is stored in the
SELECT *, OBJECT_NAME(OBJECT_ID) FROM sys.assembly_modules
It's pretty easy to generate the drop scripts from this metadata:
DECLARE @assembly_name sysname SET @assembly_name = '<your assembly name>' SELECT 'drop function ' + OBJECT_NAME(OBJECT_ID) FROM sys.assemblies AS a JOIN sys.assembly_modules AS am ON am.assembly_id = a.assembly_id WHERE a.name = @assembly_name SELECT 'drop assembly ' + @assembly_name
Generating the create scripts is much harder because there's no way to know whether the interface has changed, or the location of the assembly dll to upload.