获取SQL SERVER某个数据库中所有存储过程的参数
时 间:2017-12-13 08:21:17
作 者:宏鹏 ID:21115 城市:上海
摘 要:获取SQL SERVER某个数据库中所有存储过程的参数
正 文:
一、获取指定数据库中所有存储过程的参数的方法
Select sp.object_Id as FunctionId, sp.name as FunctionName, isnull(param.name,'')as ParamName,isnull(usrt.name,'') AS [DataType], ISNULL(baset.name, '') AS [SystemType], CAST(CASE when baset.name is null then 0 WHEN baset.name IN ('nchar', 'nvarchar') AND param.max_length <> -1 THEN param.max_length/2 ELSE param.max_length END AS int) AS [Length], '' as ParamReamrk,isnull(parameter_id,0) as SortId FROM sys.objects AS sp INNER JOIN sys.schemas b ON sp.schema_id = b.schema_id left outer JOIN sys.all_parameters AS param ON param.object_id=sp.object_Id LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = param.user_type_id LEFT OUTER JOIN sys.types AS baset ON (baset.user_type_id = param.system_type_id and baset.user_type_id = baset.system_type_id) or ((baset.system_type_id = param.system_type_id) and (baset.user_type_id = param.user_type_id) and (baset.is_user_defined = 0) and (baset.is_assembly_type = 1)) LEFT OUTER JOIN sys.extended_properties E ON sp.object_id = E.major_id Where sp.TYPE in ('FN', 'IF', 'TF','P') AND ISNULL(sp.is_ms_shipped, 0) = 0 AND ISNULL(E.name, '') <> 'microsoft_database_tools_support' orDER BY sp.name,param.parameter_id ASC
二、实例
查询SQL SERVER 系统数据库 master 中的所有存储过程参数
Access软件网QQ交流群 (群号:483923997) Access源码网店
常见问答:
技术分类:
源码示例
- 【源码QQ群号19834647...(12.17)
- 【Access窗体导出PDF】...(04.08)
- 【Access窗体导出PDF】...(04.07)
- Access两种方式实现即时更...(03.01)
- Access隐藏与显示lacc...(01.12)
- 【Access高效办公】将每个...(12.23)
- Access21点游戏源代码(12.13)
- 【Access窗体导出Exce...(11.15)
- 【Access开发】Acces...(11.14)
- 通过Access宏录入数据到选...(11.10)
学习心得
最新文章
- ACCESS精华集锦资料.CHM(04.25)
- Access VBA语句If Me...(04.24)
- 【Access修改记录示例】编辑选...(04.22)
- 【Access表名称命名建议】将A...(04.20)
- Access学习笔记--用Acce...(04.19)
- 【Access重复项查询示例】将A...(04.17)
- Access快速开发平台企业版--...(04.16)
- 【Access模块示例】通过模块代...(04.15)
- Access查询里面分组合计功能添...(04.13)
- 【Access删除查询】删除数字最...(04.12)