SQL Server数据库引擎.NET CLR环境数据库管理员向导（2）

核心提示： sys.assemblies 的“is visible”标记可以在使用公共方法注册时隐藏程序集，它可以隐藏例如利用网络服务访问数据时.XmlSerializers产生的帮助程序集，SQL Server数据库引擎.NET CLR环境数据库管理员向导（2）(2)，在sy

sys.assemblies 的“is visible”标记可以在使用公共方法注册时隐藏程序集。它可以隐藏例如利用网络服务访问数据时.XmlSerializers产生的帮助程序集。

在sys.assembly_modules视图中，一个空程序集方法显示了一个被用于用户定义聚合的程序集。需要注意的是有两个系统存储过程对这些表提供访问。如下图。

EXEC sys.sp_assemblies_rowset N'<AssemblyName>'
EXEC sys.sp_assembly_dependencies_rowset <AssemblyID>

存储过程

-- Read CLR Stored Procedure Metadata
SELECT schema_name(sp.schema_id) + '.' + sp.[name] AS [Name]
, sp.create_date
, sp.modify_date
, sa.permission_set_desc 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_desc = N'CLR_STORED_PROCEDURE'

触发器

-- CLR Trigger Metadata
-- Note that Trigger parent object is always in the same schema
SELECT schema_name(so.schema_id) + '.' + tr.[name] AS [Name]
, schema_name(so.schema_id) + '.' + object_name(tr.parent_id) AS 　[Parent]
, te.type_desc AS [Fired On]
, te.is_first
, te.is_last
, tr.create_date
, tr.modify_date
, sa.permission_set_desc AS [Access]
, tr.is_disabled
, tr.is_not_for_replication
, tr.is_instead_of_trigger
FROM sys.triggers AS tr
INNER JOIN sys.objects AS so
ON tr.[object_id] = so.[object_id]
INNER JOIN sys.trigger_events AS te
ON tr.[object_id] = te.[object_id]
INNER JOIN sys.module_assembly_usages AS mau
ON tr.object_id = mau.object_id
INNER JOIN sys.assemblies AS sa
ON mau.assembly_id = sa.assembly_id
WHERE tr.type_desc = N'CLR_TRIGGER'

用户定义函数