sql server查询可编程对象定义的方式对比以及整合

OBJECT_ID(N‘[dbo].[usp_helptext2]‘, ‘P‘) IS NOT NULL BEGIN DROP PROCEDURE [dbo].[usp_helptext2]; END GO --================================== -- 功能: 查看可编程对象定义 -- 说明: 支持用户定义类型，可以运行于SQL Server 2005+ -- 创建: yyyy-MM-dd hh:mm-hh:mm XXX 创建内容描述 -- 修改: yyyy-MM-dd hh:mm-hh:mm XXX 修改内容描述 --================================== CREATE PROCEDURE [dbo].[usp_helptext2] ( @nvcObjectName AS NVARCHAR(776) -- 对象名称，可以支持的对象类型为(C、D、FN、IF、P、R、RF、TF、TR、U、V) ,@nvcComputedColumnName AS NVARCHAR(128) = NULL -- 计算列名称（如果@nvcObjectName的对象类型为U，则该参数表示计算列名称) ) AS BEGIN SET NOCOUNT ON; SET @nvcObjectName = ISNULL(@nvcObjectName, N‘‘); IF (@nvcObjectName = N‘‘) BEGIN RAISERROR(16902, -1, -1,N‘usp_helptext2‘, N‘@nvcObjectName‘); RETURN(1); END SET @nvcComputedColumnName = ISNULL(@nvcComputedColumnName, N‘‘); DECLARE @tntRetVal AS TINYINT; SET @tntRetVal = 0; DECLARE @tblObjDef AS TABLE ( [Text] NVARCHAR(1000) NULL ); DECLARE @intObjectID AS INT ,@chaType AS CHAR(2) ,@nvcText AS NVARCHAR(MAX); SELECT @intObjectID = 0 ,@chaType = ‘‘ ,@nvcText = N‘‘; SELECT @intObjectID = [object_id] ,@chaType = [type] FROM [sys].[all_objects] WHERE [type] IN (‘C‘, ‘D‘, ‘FN‘, ‘IF‘, ‘P‘, ‘R‘, ‘RF‘, ‘TF‘, ‘TR‘, ‘U‘, ‘V‘) AND [name] = PARSENAME(@nvcObjectName, 1); IF (@nvcComputedColumnName > N‘‘) -- 获取计算列定义 BEGIN IF (@chaType NOT IN (‘S‘, ‘U‘, ‘TF‘)) BEGIN RAISERROR(15218, -1, -1, @nvcObjectName); RETURN(1); END INSERT INTO @tblObjDef ([Text]) EXEC [sys].[sp_helptext] @objname = @nvcObjectName -- nvarchar(776) ,@columnname = @nvcComputedColumnName -- sysname IF(@@ROWCOUNT = 0) BEGIN SET @tntRetVal = 1; END SELECT @nvcText = ISNULL([Text], N‘‘) FROM @tblObjDef; END ELSE IF (@intObjectID <> 0) -- 获取除计算列和服务器触发器以外的所有对象类型的定义 BEGIN SET @nvcText = OBJECT_DEFINITION(@intObjectID); IF(@@ROWCOUNT = 0) BEGIN SET @tntRetVal = 1; END END ELSE IF (@intObjectID = 0) -- 尝试获取服务器触发器定义 BEGIN SELECT @nvcText = T1.[definition] FROM [sys].[server_sql_modules] AS T1 INNER JOIN [sys].[server_triggers] AS T2 ON [T1].[object_id] = [T2].[object_id] WHERE T2.[name] = @nvcObjectName; IF(@@ROWCOUNT = 0) BEGIN SET @tntRetVal = 1; END END SELECT @nvcText AS [Text]; RETURN(@tntRetVal); END GO

 

以上存储dbo.usp_helptext2可以完全实现以上表格的所有可编程对象定义查看，不论是系统定义的还是用户定义的，前提是以上表格中的可编程对象类型定义。当然也存在缺点就是可编程对象定义输出到SSMS客户端超过最大限制（SQL Server 2012环境中的时43679双字节字符长度)就要出现截断，这个缺点可以通过代码编程来完美解决这个缺点。   3、dbo.helptext2的选择性测试
  用户定义检查约束测试： 用户定义约束测试： 系统定义存储测试： 用户定义计算列测试：   数据库DDL触发器测试 服务器触发器测试： 其他对象类型的测试不在全部列举。   4、总结语   在这次的学习和研究，sql server系统自带的视图以及存储过程针对可编程对象的实现很很完善的，不过叶分散在不同的地方，这次整合也就是将分散在不同地方的聚合在一起提供统一入口来处理。如果不想查看计算列和服务器触发器的定义以外的所有可编程对象类型的定义，建议使用object_definition函数，该函数几乎提供了很完善的功能。这次学习也发现数据库DDL触发器在sys.object是无法查看到的，需要在sys.triggers或sys.all_objects目录视图中查看到，这个也在object_id函数做了限制的。由于服务器触发器本身属于服务器的，这个sql server团队本身也是用了系统表sys.sysschobjs、sys.syspalnames 、sys.syspalvalues，虽然sys.all_objects也是用了系统表sys.syscheobjs，但是却在sys.all_objects中无法查询到的，也在object_id函数中做了限制，只能在sys.server_triggers查询到，这从逻辑上进行了分离，也符合服务器触发器的归属性质。   希望这个整合的查看可编程对象定义的存储，可以帮助到需要的人。继续精进，继续探究sql server。   5、参考清单列表

• https://msdn.microsoft.com/en-us/library/ms176112.aspx
• https://msdn.microsoft.com/en-us/library/ms175081.aspx
• https://msdn.microsoft.com/en-us/library/ms188034.aspx
• https://msdn.microsoft.com/en-us/library/ms184389.aspx
• https://msdn.microsoft.com/en-us/library/ms176090.aspx
• https://msdn.microsoft.com/en-us/library/ms188746.aspx
• https://msdn.microsoft.com/en-us/library/ms176054.aspx
• https://msdn.microsoft.com/en-us/library/ms187794.aspx

•  

sql server查询可编程对象定义的方式对比以及整合

标签：