How Can I Get The Definition Of A Sql Clr Function From The Database

The code I was given for an SQL CLR project contains one function where the .net implementation is invalid. However I have been able run a query to confirm that there is a functio

Solution 1:

There are several different SQLCLR object types and so this would take a few queries. The quickest and easiest way to get the definitions, especially for a one-time operation, is to script out the objects via SQL Server Management Studio (SSMS). You should be able to select all of each particular type in "Object Explorer Details" and script them out in all together.

Or, to do all SQLCLR objects across all types in one shot, go to the general "Generate Scripts":

1. Right-click on database in Object Explorer
2. Go to "Tasks >" submenu
3. Select "Generate Scripts..."
4. Choose "Select specific database objects"
5. Drill down to the various object types – Stored Procedures, User-Defined Functions, User-Defined Types, and User-Defined Aggregtes – and check the ones that you want to script out
6. Click "Next >"
7. Choose your preferred method of saving / viewing
8. Click "Next >"
9. Click "Next >" (again, after reviewing)
10. Click "Finish"

For just User-Defined Aggregates (UDA) and scalar User-Defined Functions (UDF), you can use the following query (keep in mind that parameter_id of 0 is the return type):

SELECT OBJECT_NAME(am.[object_id]) AS [Name], am.*, p.*, t.[name] AS [DataType]
FROM   sys.assembly_modules am
INNER JOIN sys.parameters p
        ON p.[object_id] = am.[object_id]
INNER JOIN sys.types t
        ON t.[user_type_id] = p.[user_type_id]
WHERE  OBJECT_NAME(am.[object_id]) = N'{function_name}';