Get the T-SQL CREATE statement for SQLCLR stored procedures

Question

I have an application that retrieves the text of stored procedures using sp_helptext. It works great on all my stored procedures except for CLR stored procedures. If I try to use sp_helptext on a SQLCLR stored procedure, I get this error:

There is no text for object 'PROC_NAME'

I know SSMS can do it when I use the "script as" -> "create to" -> command.

But when I trace the SSMS "generate script" action with SQL Server Profiler, it gives me a dauntingly long list of fairly complex activity. I can slog through that if I must, but does anyone know a straightforward way to programmatically get the code of a CLR stored proc?

Edit for clarification
I don't want to see the actual code from the assembly; I'm just asking for an easy way to view the T-SQL code, as in this example:

CREATE PROCEDURE [dbo].[MY_PROC]
    @PARAM1 [xml],
    @PARAM2 [uniqueidentifier],
    @PARAM3 [nvarchar](255),
    @PARAM4[bit] = False
WITH EXECUTE AS OWNER
AS
EXTERNAL NAME [SomeSolution.SomeProject].[SomeAssembly].[SomeMethod]
GO

In other words, the "SQL Server side" of the SQLCLR function.

Haplosis

Do you want just the Stored Procedures or also Functions, Types, Aggregates? – Semiotic

Answer 1

I had the same dilemma and searched over and over on the web for any solution to get the code of a CLR stored procedure. Finally had to PROFILE what SSMS "generate script" action did as you said and here is what I got:

--GET ALL CLR stored procedures
SELECT
sp.name AS [Name],
sp.object_id AS [object_ID],
case when amsp.object_id is null then N'''' else asmblsp.name end AS [AssemblyName],
case when amsp.object_id is null then N'''' else amsp.assembly_class end AS [ClassName],
case when amsp.object_id is null then N'''' else amsp.assembly_method end AS [MethodName]
FROM
sys.all_objects AS sp
LEFT OUTER JOIN sys.assembly_modules AS amsp ON amsp.object_id = sp.object_id
LEFT OUTER JOIN sys.assemblies AS asmblsp ON asmblsp.assembly_id = amsp.assembly_id
LEFT OUTER JOIN sys.procedures AS spp ON spp.object_id = sp.object_id
WHERE spp.type like 'PC'

--For each CLR SP get the parameters in use
SELECT
param.name AS [Name]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
WHERE sp.name like 'your_sp_name' order by param.parameter_id ASC

--For each parameter get the values, data type and so on...
SELECT
param.name AS [Name],
param.parameter_id AS [param_ID],
sp.object_id AS [object_ID],
param.default_value AS [DefaultValue],
usrt.name AS [DataType],
sparam.name AS [DataTypeSchema],
ISNULL(baset.name, N'''') AS [SystemType],
CAST(CASE WHEN baset.name IN (N'nchar', N'nvarchar') AND param.max_length <> -1 THEN         param.max_length/2 ELSE param.max_length END AS int) AS [Length],
CAST(param.precision AS int) AS [NumericPrecision],
CAST(param.scale AS int) AS [NumericScale]
FROM
sys.all_objects AS sp
INNER JOIN sys.all_parameters AS param ON param.object_id=sp.object_id
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id
LEFT OUTER JOIN sys.schemas AS sparam ON sparam.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and     baset.user_type_id = baset.system_type_id) 
WHERE param.name='@param1' and sp.name='your_sp_name'

With this scripts I made a Perl script to generate the code for me. I guess from here you could do the same or create your own stored procedure to print the desired code. I'm not a SQL programmer so I don't know how to do that, but if someone does the programming of the above queries, please share it.

Swordfish

Thanks, this is the only correct answer to the actual question. – Photina

Answer 2

A CLR stored procedure will not have text as such as it is a link to a DLL. You will need to get the source code to the DLL

See Common Language Runtime (CLR) Integration Programming Concepts for example

Angelangela

Upvote for you - you are quite right, and I realize now that my question was a bit unclear. Please see my more recent revision. – Haplosis

Answer 3

Short answer: no, you can't do that. (At least not programatically/easily) CLR procedures are loaded from .NET assemblies (binary files) and there is no simple way to get the source code of such file. Not within SQL Server. But you can use tools like RedGate Reflector to disassemble DLL and view/recover source code of the stored procedure.

Yeo

Upvote for you - you are quite right, and I realize now that my question was a bit unclear. Please see my more recent revision. – Haplosis

Answer 4

1. Unlike similar T-SQL objects, the CREATE [ STORED PROCEDURE | FUNCTION | TRIGGER | TYPE | AGGREGATE ] ... statements for SQLCLR objects are not stored as-is; they are derived through the object type and a combination of sys.assemblies, sys.assembly_modules, sys.parameters, sys.types, and sys.assembly_types.

   If you are trying to construct these CREATE statements in T-SQL or some other non-.NET language, then you need to select the appropriate data from those tables and piece it all together. However, if you are using .NET, then you can make use of the Scripter Class in the SMO library ( Microsoft.SqlServer.Smo.dll ). It should be able to script anything in SQL Server. And in fact, you should probably use it to script everything for your tool instead of using queries.

2. Outside of viewing stored procedure / function / trigger definitions on the fly in SSMS (i.e. quick and easy), you should not be using sp_helptext to extract object definitions. It does a lot of unnecessary work as it appears to be only minorly updated since SQL Server 2000. The two main issues with it are:

   1. It does not handle individual lines that are > 4000 characters long
   2. It only allows for CRLF (i.e. \r\n ) newlines, not LF-only newlines (i.e. \n ) which are valid.
      
      

   You can see this by viewing the definition of that system stored procedure:

   EXEC sp_helptext 'sp_helptext';
   

   Instead, you should use either the OBJECT_DEFINITION() built-in function (which takes an object_id as input) or select from one of the system catalog views that contain these definitions: sys.sql_modules, sys.server_sql_modules, sys.system_sql_modules, and sys.system_sql_modules.

Semiotic

Answer 5

We had an issue of changes not getting added to source control so I did the following to get the updates.

1. using sqlservermanager I queried all the sys.assembly_files to find what I needed.

2. wrote a quick console program to get the source for the files that were changed.

   • In our case it was for records having an assembly_id of 68541 and multiple files all having a file_id greater than 3

   • the source for the cs file is in a column named content and the file name is in a column named name. I converted the content to a byte array then a string and wrote the content to a text file using filename.txt.

   • copy and past the updated code and push to source control.

     using (SqlConnection conn = new SqlConnection(connstring))
     {
         using (SqlCommand cmd = new SqlCommand("SELECT * FROM sys.assembly_files WHERE assembly_id = 68541 and file_id > 3", conn))
         {
             DataTable ds = new DataTable();
             using (SqlDataAdapter da = new SqlDataAdapter(cmd))
             {
                 da.Fill(ds);
                 foreach (DataRow r in ds.Rows)
                 {
                     byte[] binaryString = (byte[])r["content"];
                     string x = Encoding.UTF8.GetString(binaryString);
                     string filename = @"C:\SQLCLR";
                     string filePath = string.Format(@"{0}\{1}.txt", filename,r["name"]);
                     File.WriteAllText(filePath, x);
     
                 }
             }
         }
     }
     

Areopagus

Actually this is a very cool answer since it will place the file on your PC rather than on the DB Server. I was able to run it right from LinqPad by inserting a value for "connstring" and updating the query with my "assembly_id". I also changed "File.WriteAllText(filePath, x);" to "File.WriteAllBytes(filePath, binaryString);" since the data is binary and I wanted to run it through ILSpy which worked perfectly. – Doublestop

Thanks a bunch. This got me out of a jam. We seemed to have not checked in the actual source code, as such the dll in production differed quite a bit from the source code we had in source control. Though I will suggest edit the code as follows for the dll to be actually loadable : code string filename = @"C:\SQLCLR"; // string filePath = string.Format(@"{0}\{1}.txt", filename,"NameOfYourDll.dll"); File.WriteAllBytes(filePath, binaryString); – Dobsonfly

Answer 6

Actually, you can do this - by inspecting the sys.assembly_files catalog view:

SELECT CONVERT(VARCHAR(MAX), content) as my_source_code 
FROM sys.assembly_files

Stavanger

That just returns a set of rows that all say "MZ" on my system – Haplosis

What's MZ? That's what I get. – Suppurative

Answer 7

To extract the assambly run this :

DECLARE @IMG_PATH VARBINARY(MAX)
DECLARE @ObjectToken INT

SELECT @IMG_PATH = content FROM sys.assembly_files WHERE assembly_id = 65536

EXEC sp_OACreate 'ADODB.Stream', @ObjectToken OUTPUT
    EXEC sp_OASetProperty @ObjectToken, 'Type', 1
    EXEC sp_OAMethod @ObjectToken, 'Open'
    EXEC sp_OAMethod @ObjectToken, 'Write', NULL, @IMG_PATH
    EXEC sp_OAMethod @ObjectToken, 'SaveToFile', NULL, 'D:\SqlServerProject1.dll', 2
    EXEC sp_OAMethod @ObjectToken, 'Close'
    EXEC sp_OADestroy @ObjectToken

Then decompile it with Redgate Reflector or any .Net decompiler (ilspy, ...)

Tchao

Answer 8

The question is quite old, but there are no other sources in the internet, just instructions how to use SSMS scripting.

As Alex Castillo, I also profiled and reverse-engineered SSMS scripting procedures and then combined it all in one T-SQL query for convenience. Here is what I got:

SELECT 
    'DROP ' +
    CASE o.[type]
        WHEN 'FT' THEN 'FUNCTION'
        WHEN 'FS' THEN 'FUNCTION'
        WHEN 'PC' THEN 'PROCEDURE'
    END + ' [' + SCHEMA_NAME(ao.[schema_id]) + '].[' + o.[name] + '];' AS [drop_statement]
    ,'CREATE ' +
    CASE o.[type]
        WHEN 'FT' THEN 'FUNCTION'
        WHEN 'FS' THEN 'FUNCTION'
        WHEN 'PC' THEN 'PROCEDURE'
    END + ' [' + SCHEMA_NAME(ao.[schema_id]) + '].[' + o.[name] + ']  ' +
    CASE o.[type]
        WHEN 'FT' THEN '(' + COALESCE(inp.[params_def], '') + ') RETURNS TABLE (' + oc.[columns_def] + ')'
        WHEN 'FS' THEN '(' + COALESCE(inp.[params_def], '') + ') RETURNS ' + t.[name] + CASE WHEN t.[name] IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar', 'datetime2') THEN '(' + CASE WHEN t.[name] IN ('nchar', 'nvarchar') AND aop.[max_length] != -1 THEN CAST(aop.[max_length] / 2 AS [varchar]) WHEN t.[name] IN ('nvarchar') AND aop.[max_length] = -1 THEN 'MAX' WHEN t.[name] = 'datetime2' THEN CAST(aop.[scale] AS [varchar]) ELSE CAST(aop.[max_length] AS [varchar]) END  + ')' WHEN t.[name] IN ('numeric', 'decimal') THEN '(' + CAST(aop.[precision] AS [varchar]) + ', ' + CAST(aop.[scale] AS [varchar]) + ')' ELSE '' END
        WHEN 'PC' THEN COALESCE(inp.[params_def], '')
    END + ' WITH EXECUTE AS CALLER AS ' +
    ' EXTERNAL NAME [' + a.[name] COLLATE SQL_Latin1_General_CP1_CI_AS + '].[' + am.[assembly_class] + '].[' + am.[assembly_method] + '];' AS [create_statement]
FROM sys.assemblies a WITH(NOLOCK)
JOIN sys.assembly_modules am WITH(NOLOCK) ON a.[assembly_id] = am.[assembly_id]
JOIN sys.objects o WITH(NOLOCK) ON am.[object_id] = o.[object_id]
LEFT JOIN sys.all_parameters AS aop WITH(NOLOCK) ON aop.[object_id] = am.[object_id] AND aop.[is_output] = 1
LEFT JOIN sys.types AS t WITH(NOLOCK) ON (t.[user_type_id] = aop.[system_type_id] AND t.[user_type_id] = t.[system_type_id]) OR ((t.[system_type_id] = aop.[system_type_id]) AND (t.[user_type_id] = aop.[user_type_id]) AND (t.[is_user_defined] = 0) AND (t.[is_assembly_type] = 1)) 
JOIN sys.all_objects ao WITH(NOLOCK) ON o.[object_id] = ao.[object_id]
OUTER APPLY (
    SELECT
        SUBSTRING((SELECT
        ', ' + aip.[name] + ' ' + it.[name] + CASE WHEN it.[name] IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar', 'datetime2') THEN '(' + CASE WHEN it.[name] IN ('nchar', 'nvarchar') AND aip.[max_length] != -1 THEN CAST(aip.[max_length] / 2 AS [varchar]) WHEN it.[name] IN ('nvarchar') AND aip.[max_length] = -1 THEN 'MAX' WHEN it.[name] = 'datetime2' THEN CAST(aip.[scale] AS [varchar]) ELSE CAST(aip.[max_length] AS [varchar]) END  + ')' WHEN it.[name] IN ('numeric', 'decimal') THEN '(' + CAST(aip.[precision] AS [varchar]) + ', ' + CAST(aip.[scale] AS [varchar]) + ')' ELSE '' END AS [params_def]
    FROM sys.all_parameters AS aip WITH(NOLOCK)
    LEFT JOIN sys.types AS it WITH(NOLOCK) ON (it.[user_type_id] = aip.[system_type_id] AND it.[user_type_id] = it.[system_type_id]) OR ((it.[system_type_id] = aip.[system_type_id]) AND (it.[user_type_id] = aip.[user_type_id]) AND (it.[is_user_defined] = 0) AND (it.[is_assembly_type] = 1)) 
    WHERE 1 = 1
        AND aip.[is_output] = 0
        AND aip.[object_id] = am.[object_id]
    ORDER BY aip.[parameter_id]
    FOR XML PATH, TYPE).value('.[1]', 'nvarchar(MAX)'), 3, 2147483647) AS [params_def]
) AS inp
OUTER APPLY (
    SELECT 
        SUBSTRING((SELECT
            ', [' + ac.[name] + '] ' + ct.[name] + CASE WHEN ct.[name] IN ('char', 'varchar', 'binary', 'varbinary', 'nchar', 'nvarchar', 'datetime2') THEN '(' + CASE WHEN ct.[name] IN ('nchar', 'nvarchar') AND ac.[max_length] != -1 THEN CAST(ac.[max_length] / 2 AS [varchar]) WHEN ct.[name] IN ('nvarchar') AND ac.[max_length] = -1 THEN 'MAX' WHEN ct.[name] = 'datetime2' THEN CAST(ac.[scale] AS [varchar]) ELSE CAST(ac.[max_length] AS [varchar]) END  + ')' WHEN ct.[name] IN ('numeric', 'decimal') THEN '(' + CAST(ac.[precision] AS [varchar]) + ', ' + CAST(ac.[scale] AS [varchar]) + ')' ELSE '' END + CASE ac.[is_nullable] WHEN 0 THEN ' NOT' ELSE '' END + ' NULL'
        FROM sys.all_columns ac
        LEFT JOIN sys.types AS ct WITH(NOLOCK) ON (ct.[user_type_id] = ac.[system_type_id] AND ct.[user_type_id] = ct.[system_type_id]) OR ((ct.[system_type_id] = ac.[system_type_id]) AND (ct.[user_type_id] = ac.[user_type_id]) AND (ct.[is_user_defined] = 0) AND (ct.[is_assembly_type] = 1)) 
        WHERE 1 = 1
            AND ac.[object_id] = am.[object_id]
        ORDER BY ac.[column_id]
        FOR XML PATH, TYPE).value('.[1]', 'nvarchar(MAX)'), 3, 2147483647) AS [columns_def]
) AS oc
WHERE 1 = 1
    AND a.[name] = <'your assembly name'>
    AND SCHEMA_NAME(ao.[schema_id]) = <'your schema name'>

One just needs to execute the query and copy-paste value from corresponding output row into a new query window and execute. The query covers quite many aspects, but not, for example, user-defined types, if you have any in your SQL CLR routines. It is not that difficult to adjust, i just do not use SQL CLR UDT in my code. I hope someone will find it useful.

Embrasure

Answer 9

Its pretty easy - if you have access to SQL Server Management Studio.

Right click the CLR Stored Procedure and then select CREATE SCRIPT - voilla.

Happy CLRing.

-Danish.

Bag

As I said in the question: "I know SSMS can do it when I use the "script as" -> "create to" -> command.". – Haplosis