分别检索存储过程脚本和参数 [英] Retrieving stored procedure script and parameters separately
问题描述
我想使用 C# 访问存储过程脚本.
I want to access the stored procedure script using C#.
我使用了 EXEC sp_HelpText STOREDPROCNAME
.这工作正常.
I used EXEC sp_HelpText STOREDPROCNAME
. This is working fine.
有没有办法分别检索存储过程查询和存储过程参数??
Is there any way to retrieve stored procedure query and the stored procedure parameter separately??
例如:我需要:
delete from [dbo].[tblTransactions] where [ID] = @ID
和
@ID numeric(18,0)
分开.
如何做到这一点?
推荐答案
您可以通过这种方式获取正文,但无法解析单个语句:
You can get the body this way, but there isn't a way to parse out the individual statements:
SELECT definition FROM sys.sql_modules
WHERE [object_id] = OBJECT_ID('dbo.procedurename');
您可以通过以下方式获取其参数:
You can get its parameters by:
SELECT name, system_type_id, max_length, precision, scale
FROM sys.parameters
WHERE [object_id] = OBJECT_ID('dbo.procedurename');
请注意,如果没有对定义进行暴力解析,您无法确定参数是否具有默认值,如果有,默认值是什么.您可以更好地使用正则表达式或其他解析方法在 C# 中执行这些操作,或者像 我在这里开始的 ParamParser 项目这样的 PowerShell.
Note that you can't determine without brute force parsing of the definition whether the parameters have a default value, and if they do, what the default value is. You are better equipped to do these things in C# using RegEx or other parsing methods, or PowerShell like the ParamParser project I started here.
这篇关于分别检索存储过程脚本和参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!