tsunami

log in
history

SQL 2005: list SQLCLR assemblies on server

Luke Breuer
2010-08-31 20:50 UTC

intro
It can be a bit arduous to see which assemblies are installed where on a given SQL Server instance. The script below does exactly this, with the added bonus of showing you where the same assemblies are installed in multiple databases. If Visual Studio were used to deploy the code, you will likely get an extended property that gives the path from which the code was deployed.
code
-- the try block suppresses an irrelevant error message
begin try
    drop table #assemblies
end try
begin catch
end catch


create table #assemblies (
    database_name        sysname        not null,
    assembly_name        sysname        not null,
    clr_name             nvarchar(4000),
    permission_set_desc  nvarchar(60),
    create_date          datetime       not null,
    property_name        nvarchar(128),
    property_value       sql_variant,
    checksum             bigint         not null,
)
go

/*
select * from master.sys.all_objects where name like '%assembly%'
select  assembly_id, name, file_id, datalength(content) from sys.assembly_files
*/


declare c cursor local fast_forward for
select  name
from    sys.databases
where   state_desc = 'ONLINE' -- this shouldn't be surprising

open c

declare @name sysname

fetch next from c into @name

while @@fetch_status = 0
begin
    declare @sql nvarchar(4000)
    set @sql = replace('
        with Checksums as (
            select  assembly_id, checksum = sum(cast(checksum(content) as bigint))
            from    ?.sys.assembly_files
            group by assembly_id
        )
        insert #assemblies
        select  database_name = ''?'', assembly_name = a.name, a.clr_name, 
                a.permission_set_desc, a.create_date, property_name = ep.name, 
                property_value = ep.value, checksum = c.checksum
        from    ?.sys.assemblies a 
        inner join Checksums c on c.assembly_id = a.assembly_id
        left join ?.sys.extended_properties ep on 
                ep.major_id = a.assembly_id
            and ep.name = ''SqlAssemblyProjectRoot''', '?', @name)
    
    exec sp_executesql @sql

    fetch next from c into @name
end

close c
go

-- the "version" column below exists simply so we don't have to look at ugly checksum values
;with A as (
    select  database_name, 
            assembly_name, 
            clr_name, 
            ["version"] = dense_rank() over (partition by assembly_name order by checksum), 
            permission_set_desc, 
            property_name, 
            property_value, 
            create_date = convert(varchar(10), create_date, 20)
    from    #assemblies
)
select  p.*
from    A
pivot   (max(create_date) for database_name in (here insert_list_of_databases))p
/* generate the list of databases (that have any SQLCLR assemblies deployed)    
select dbo.JoinWithCommaSpace(distinct replace('[?]', '?', database_name)) from #assemblies
*/
JoinWithCommaSpace in the comment above is not required; it is used because the author of this article is lazy.
sample results
(after putting in a proper list of databases)
(severely truncated)
assembly_name   clr_name              "ver permi pro property_va chc_dat hb1_dat umc_dat
--------------- --------------------- ---- ----- --- ----------- ------- ------- -------
GetWebSQL       getwebsql, version=0. 1    SAFE_ Sql C:\Users\mq NULL    2009-06 NULL   
GetWebSQL       getwebsql, version=0. 2    EXTER Sql C:\Document NULL    NULL    NULL   
HB1_SqlClr      hb1_sqlclr, version=0 1    UNSAF Sql C:\hb1\trun NULL    2009-08 NULL   
SqlQueryBuilder sqlquerybuilder, vers 1    UNSAF Sql C:\hb1\trun NULL    2009-08 NULL   
SqlQueryBuilder sqlquerybuilder, vers 2    UNSAF Sql C:\hb1\trun 2009-08 NULL    NULL   
SqlServerDebug  sqlserverdebug, versi 1    SAFE_ Sql c:\Docs\c#\ NULL    2007-07 NULL   
SqlServerTest   sqlservertest, versio 1    UNSAF Sql C:\DotNET\S NULL    NULL    NULL   
SqlServerUtil   sqlserverutil, versio 1    SAFE_ Sql C:\svn\vale 2009-08 NULL    2009-08
SqlServerUtil   sqlserverutil, versio 2    SAFE_ Sql C:\Document NULL    2009-01 NULL   
SqlServerUtil   sqlserverutil, versio 3    UNSAF Sql C:\Document NULL    NULL    NULL   
System.Core     system.core, version= 1    UNSAF NUL NULL        2009-07 2007-12 2009-07