none
powershell to drop functions RRS feed

  • Question

  • HI

    I have requirement to drop the assembly functions and recreate it

    $selfunc = invoke-sqlcmd -server $Instance -Database $db.Name -Query  "SELECT   schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] FROM sys.objects o INNER JOIN sys.module_assembly_usages ma  ON o.object_id = ma.object_id INNER JOIN sys.assemblies a ON ma.assembly_id = a.assembly_id WHERE a.name=MYASSEMBLY"

    foreach($sf in $selfunc)

    {

    drop function $sf.CLRObjectName

    }

    IT IS NOT WORKING .

    Kindly help how can I achive this

    SUpersent

    Thursday, October 17, 2019 1:44 PM

All replies


  • $selfunc = invoke-sqlcmd -server $Instance -Database $db.Name -Query  "SELECT   schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName] FROM sys.objects o INNER JOIN sys.module_assembly_usages ma  ON o.object_id = ma.object_id INNER JOIN sys.assemblies a ON ma.assembly_id = a.assembly_id WHERE a.name=MYASSEMBLY"


    Did you check the value of $selfunc variable? Does it list the required values? 

    I guess you are missing the single quotes around MYASSEMBLY

    SELECT schema_name(o.schema_id) + '.' + o.[name] AS [CLRObjectName]
    FROM sys.objects o
    INNER JOIN sys.module_assembly_usages ma ON o.object_id = ma.object_id
    INNER JOIN sys.assemblies a ON ma.assembly_id = a.assembly_id
    WHERE a.name = 'MYASSEMBLY' --> MYASSEMBLY in single quotes 


    Regards,
    Vaibhav

    Thursday, October 17, 2019 1:54 PM
  • drop function $sf.CLRObjectName

    Because you drop only the function, not the assembly: Dropping an Assembly

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, October 17, 2019 2:16 PM