Find All CLR on SQL Server

 1-- Select all CLRs on a database
 2SELECT distinct
 3	DB_NAME() AS DatabaseName,
 4	udf.type AS Type,
 5	udf.name AS Name,
 6	udf.object_id AS ID,
 7	udf.create_date AS CreateDate,
 8	udf.modify_date AS DateLastModified,
 9	ISNULL(sudf.name, N'') AS Owner,
10	CAST(CASE WHEN udf.principal_id IS NULL THEN 1
11	                                        ELSE 0 END AS bit) AS IsSchemaOwned,
12	SCHEMA_NAME(udf.schema_id) AS [Schema],
13	CAST(CASE WHEN udf.is_ms_shipped = 1                                THEN 1
14	          WHEN (SELECT
15			major_id
16		FROM sys.extended_properties
17		WHERE major_id = udf.object_id
18			AND minor_id = 0
19			AND class = 1
20			AND name = N'microsoft_database_tools_support') IS NOT NULL THEN 1
21	                                                                    ELSE 0 END AS bit) AS IsSystemObject,
22 
23	CAST(ISNULL(OBJECTPROPERTYEX(udf.object_id, N'ExecIsAnsiNullsOn'), 0) AS bit) AS AnsiNullsStatus,
24	CAST(ISNULL(OBJECTPROPERTYEX(udf.object_id, N'IsSchemaBound'), 0) AS bit) AS IsSchemaBound,
25	--CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1
26	--                                                                   ELSE 0 END AS bit)
27	--AS IsEncrypted,
28	CASE WHEN amudf.object_id IS NULL THEN N''
29	                                  ELSE asmbludf.name END AS AssemblyName,
30	CAST(ISNULL(ASSEMBLYPROPERTY(asmbludf.name, N'PublicKey'), CONVERT(varbinary(8000),
31	N'')) AS varbinary(8000)) AS PublicKey,
32	CAST(ASSEMBLYPROPERTY(asmbludf.name, N'VersionMajor') AS int) AS VersionMajor,
33	CAST(ASSEMBLYPROPERTY(asmbludf.name, N'VersionMinor') AS int)
34	AS VersionMinor,
35	CAST(ASSEMBLYPROPERTY(asmbludf.name, N'VersionBuild') AS int) AS VersionBuild,
36	CAST(ASSEMBLYPROPERTY(asmbludf.name, N'VersionRevision') AS int) AS VersionRevision,
37	CAST(CASE WHEN asmbludf.assembly_id < 65536 THEN 1
38	                                            ELSE 0 END AS bit) AS IsSystemObject,
39	CASE WHEN amudf.object_id IS NULL THEN N''
40	                                  ELSE amudf.assembly_class END AS ClassName,
41	CASE WHEN amudf.object_id IS NULL THEN N''
42	                                  ELSE amudf.assembly_method END AS MethodName,
43	CAST(CASE WHEN amudf.object_id IS NULL THEN CAST(smudf.null_on_null_input AS bit)
44	                                       ELSE amudf.null_on_null_input END AS bit) AS ReturnsNullOnNullInput,
45	CASE WHEN amudf.object_id IS NULL THEN CASE isnull(smudf.execute_as_principal_id, - 1) WHEN - 1 THEN 1
46		                                                                                   WHEN - 2 THEN 2
47		                                                                                            ELSE 3 END
48	                                  ELSE CASE isnull(amudf.execute_as_principal_id, - 1) WHEN - 1 THEN 1
49		                                                                                   WHEN - 2 THEN 2
50		                                                                                            ELSE 3 END END AS ExecutionContext,
51	CASE WHEN amudf.object_id IS NULL THEN ISNULL(user_name(smudf.execute_as_principal_id), N'')
52	                                  ELSE ISNULL(user_name(amudf.execute_as_principal_id), N'') END AS ExecutionContextPrincipal,
53	CAST(OBJECTPROPERTYEX(udf.object_id, N'IsDeterministic') AS bit) AS IsDeterministic,
54	(CASE WHEN 'FN' = udf.type THEN 1
55	      WHEN 'FS' = udf.type THEN 1
56	      WHEN 'IF' = udf.type THEN 3
57	      WHEN 'TF' = udf.type THEN 2
58	      WHEN 'FT' = udf.type THEN 2
59	                           ELSE 0 END) AS FunctionType,
60	CASE WHEN udf.type IN ('FN', 'IF', 'TF') THEN 1
61	     WHEN udf.type IN ('FS', 'FT')       THEN 2
62	                                         ELSE 1 END AS ImplementationType,
63	CAST(ISNULL(OBJECTPROPERTYEX(udf.object_id, N'ExecIsQuotedIdentOn'), 0) AS bit)
64	AS QuotedIdentifierStatus 
65	--,
66	--CAST(0 AS bit) AS IsInlineable,
67	--ISNULL(sm.uses_native_compilation, 0) AS IsNativelyCompiled,
68	--ISNULL(smudf.definition, ssmudf.definition) AS Definition
69FROM sys.all_objects AS udf                     
70LEFT OUTER JOIN sys.database_principals AS sudf 
71	ON sudf.principal_id = ISNULL(udf.principal_id, OBJECTPROPERTY(udf.object_id, 'OwnerId'))
72LEFT OUTER JOIN sys.sql_modules AS smudf        
73	ON smudf.object_id = udf.object_id
74LEFT OUTER JOIN sys.system_sql_modules AS ssmudf
75	ON ssmudf.object_id = udf.object_id
76LEFT OUTER JOIN sys.assembly_modules AS amudf   
77	ON amudf.object_id = udf.object_id
78LEFT OUTER JOIN sys.assemblies AS asmbludf      
79	ON asmbludf.assembly_id = amudf.assembly_id
80LEFT OUTER JOIN sys.all_sql_modules AS sm       
81	ON sm.object_id = udf.object_id
82CROSS JOIN sys.types AS baset                   
83WHERE (asmbludf.name IS NOT NULL)