SQL Server CLR function migration how to extract code from assembly

pxiryf3j  于 2023-11-16  发布在  其他
关注(0)|答案(2)|浏览(137)

I need to migrate instance of SQL Server and found that I don't have a source code for a few CLR functions anywhere, I see functions in assembly and know their logic:

select * from sys.assembly_modules

Is it possible to extract source code from it, so I will store it in newly added TFS.

Actually now main task is to have same CLR functions on a new server, so probably just migrating assemblies will do the job? Or this will require do some extra steps?

And on step2 I will probably will try to recreate source code if reverse engineering won't work.

Thanks

9rbhqvlz

9rbhqvlz1#

An SQLCLR function is part of an assembly. An assembly can contain multiple functions. It can (in theory) be made up of multiple files, although that is unusual.

You can get the actual DLL files with this

SELECT
  a.name AssemblyName,
  f.name FileName,
  f.content Dll
FROM sys.assemblies a
JOIN sys.assembly_files f ON f.assembly_id = a.assembly_id
-- if you want to filter to certain SQLCLR functions
/*
WHERE a.assembly_id IN (
    SELECT *
    FROM sys.assembly_modules m
    WHERE m.object_id = OBJECT_ID(N'YourFunction')
)
*/

You can de-compile the DLLs with your favorite .NET decompiler.

If you want to just transfer them, you can commit them to source control as CREATE ASSEMBLY FROM followed by the binary data

vdzxcuhz

vdzxcuhz2#

Making Charlieface's second recommendation more explicit, just transfer the assembly. You can do this through SSMS by choosing the assembly in Object Explorer and selecting "Script Assembly as..." from the context menu. Of course, since that just uses SMO on the back end, you can also do this with a little scripting. Here's a quick-and-dirty powershell script ( Get-DbaDatabase coming from the always delightful dbatools module).

$db = Get-DbaDatabase -SqlInstance yourServer -Database yourDB;
foreach ($a in ($db.Assemblies | where IsSystemObject -eq $false)){
    $a.Script();
}

相关问题