How can I get the definition of a CLR SQL function from the database
The code I gave for a SQL CLR project contains one feature that does not have a .net implementation . However, I was able to execute a request to confirm that there is an invalid named function on the server.
Schema name assembly_name assembly_class assembly_method permission_set_desc type_desc dbo ConvertFromUTC database AppName.Database.Functions ConvertFromUTC UNSAFE_ACCESS CLR_SCALAR_FUNCTION
This is enough information to confirm the deployed version is a scalar function and not a table as intended.
Is there a way that I can get the I / O parameters from the version loaded into the database to ensure that the correct version is what my previous question suggested and not something else? More generally, I would like to get the same results for all other CLR functions in the database, to see if any of them are inconsistencies compared to the code that was also provided to me.
source to share
There are several different types of SQLCLR objects, so this will require multiple queries. The fastest and easiest way to get definitions, especially for one-off work, is to script from objects via SQL Server Management Studio (SSMS). You should be able to select everything from each specific type in the "Object Browser Details" and script them together.
Or, to make all SQLCLR objects for all types in one snapshot, go to the general Generate Scripts section:
- Right click on the database in Object Explorer
- Go to the "Tasks>" section. submenu
- Select "Generate Scripts ..."
- Select "Select Specific Database Objects"
- Go to different object types - Stored Procedures, UDFs, UDTs and UDA Attributes - and check the ones you want to script out
- Click Next.
- Choose your preferred save / view method
- Click Next.
- Click "Next>" (again after viewing)
- Click Finish
For Custom Aggregates (UDA) and Scalar User Defined Functions (UDF) only, 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}';
source to share