Cataloging Assemblies and CLR Database Objects

An important part of the management of assemblies and CLR database objects is to be able to find out what is installed on the database—and what is not.

Cataloging Assemblies

The place to start is the sys.assemblies catalog view that contains information about the assembly names, identifiers, permissions, and visibility.

 SELECT * FROM sys.assemblies 

The result of the query will be something like Table 11-1.

Table 11-1: Assemblies in a Database

Name	principal id	assembly_id	clr_name	permission_set	permission_set_dcsc	is_visible	create_date	modify_date
VbSqlServerSp	1	65538	vbsqlserversp, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil	1	SAFE_ACCESS	1	2005-10-30 22:04:33.297	2005-10-30 22:04:33.513
CShrpSqlServerSp	1	65540	cshrpsqlserversp, version=0.0.0.0, culture=neutral, publickeytoken=null, processorarchitecture=msil	1	SAFE_ACCESS	1	2005-10-30 22:11:44.107	2005-10-30 22:11:44.357

There is a system stored procedure that provides access to this and other related tables. The sys.sp_assemblies_rowset procedure returns information about the assembly along with its binary. Figure 11-6 shows the result. The result may contain multiple rows for each file that is larger than 8,000 bytes.

Figure 11-6: Rowset of assemblies

Cataloging Procedures

You have probably noticed that I sometimes use the word procedure in a broader context—to reference all types of programmable database objects (such as stored procedures, functions, and triggers). Some parts of BOL refer to them as modules. I think that both BOL and I should have chosen different terms. In the software development books that I learned from, modules were larger sets of programmable objects (such as C# classes as sets of methods and properties, C libraries as sets of functions, and Visual Basic 6 modules as files that contain subroutines and functions). On the other hand, the distinction between procedure and stored procedure is too subtle.

In SQL Server 2005, you can query for modules (programmable database objects) and assembly modules (CLR database objects):

 SELECT * FROM sys.assembly_modules 

An example of a result is shown in Table 11-2.

Table 11-2: Assembly Modules

object_id	assembly_id	assembly_class	assembly_method	null_on_null_input	execute_as_principal_id
1086626914	65538	VbSqIServerSp.StoredProcedures	ap_FirstVB	0	NULL
1118627028	65540	StoredProcedures	ap_First	0	NULL

To read metadata that is collected in a database about a database object, such as a stored procedure, you can use the following query:

 SELECT schema_name(sp.schema_id) + '.' + sp.[name] AS [Name]      , sp.create_date      , sp.modify_date      , sa.permission_set_dcsc AS [Access]      , sp.is_auto_executed FROM sys.procedures AS sp      INNER JOIN sys.module_assembly_usages AS sau           ON sp.object_id = sau.object_id      INNER JOIN sys.assemblies AS sa           ON sau.assembly_id = sa.assembly_id WHERE sp.type_dcsc = N'CLR_STORED_PROCEDURE' 

Version of .NET Framework

You can find out which version of CLR is loaded in SQL Server by querying the version of the Microsoft .NET Runtime Execution Engine module:

 SELECT mdl.product_version FROM sys.dm_os_loaded_modules AS mdl WHERE mdl.[name] LIKE N'%\MSCOREE.DLL'