1

I've created an application to synchronize Microsoft SQL Server databases with the same structure. One of the tasks I need to solve is to transfer any database routine (procedure, function, trigger, etc) from source database to target one. For routine transfer I use a query

SELECT [definition] FROM sys.sql_modules WITH (NOLOCK) WHERE object_id = OBJECT_ID('SOME_OBJECT_ID')

or

SELECT * FROM INFORMATION_SCHEMA.ROUTINES where routine_name like '%SOME_ROUTINE_NAME%'

And it works perfectly for all the routines except CLR functions (the functions with [type_desc] = CLR_TABLE_VALUED_FUNCTION or [type_desc] = CLR_SCALAR_FUNCTION).

Their text is not stored in sys.sql_modules datatable and system view INFORMATION_SCHEMA.ROUTINES contains values [ROUTINE_BODY] = EXTERNAL and [ROUTINE_DEFINITION] = NULL.

But I'm sure that CLR function text is not recreated every time I open it because it can be changed and saved with user changes.

So I'd be very grateful for any hint about CLR function text location.

UPDATE: I don't needed to transfer .NET library function itself, I just want to transfer the wrapper function that is created manually in Programmability > Functions > Table-valued Functions.

Improve this question
4
  • CLR functions are assembly(dll) created in .NET and registered in SQL server. you can get the list from assembly sys.assemblies. so you will not get the text definition for CLR function inside SQL Server. Commented Sep 15, 2017 at 7:18
  • @RahulRichhariya, I don't want to transfer CLR function (= .NET library function), I asked a question about CLR_TABLE_VALUED_FUNCTION or CLR_SCALAR_FUNCTION - objects you can find in Programmability > Functions > Table-valued Functions list. And you can edit and save this object like any standard function (SQL_SCALAR_FUNCTION). Commented Sep 15, 2017 at 7:36
  • So, you're trying to generate the equivalent of, say, CREATE FUNCTION [dbo].[len_s] (@str nvarchar(4000)) RETURNS bigint AS EXTERNAL NAME [SurrogateStringFunction].[Microsoft.Samples.SqlServer.SurrogateStringFunction].[LenS];? Commented Sep 15, 2017 at 7:54
  • @Damien_The_Unbeliever, you're quite right. Commented Sep 15, 2017 at 8:28

2 Answers 2

Reset to default
1

If you want to look into CLR function or procedure you need to perform some steps as CLR functions an procedures are created from assemblies.

Step-1:- Export your assemblies to file system

Step-2:- Use .net assembly Decompiler like http://ilspy.net/ or redgate reflector

This method will work if assembly creator has not performed addition steps to prevent de-compilation.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I don't need to transfer .NET library between databases, I want to transfer CLR wrapper function that is created in Programmability > Functions > Table-valued Functions. Please look at @Damien_The_Unbeliever's comment, he's got the point.
ok got you. Are you fine with using powershell or do you want to use sql queries only?
I prefer using SQL queries but I'd be very grateful for any solution you can provide. But it sounds very strange for me that all the queries are located in sys.sql_modules datatable and can be easily selected and CLR wrapping queries (that look the same way and can be changed in the same way) need to be extracted using PowerShell.
0

Server Profiler has shown 35 different dynamic queries used by Management Studio to build the CLR wrapper function. So function text is unfortunately built on-the-fly and nothing is stored in the depths of system databases.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.