I am using Microsoft SQL Server 2008. I have a stored procedure. Is there a simple query I can execute that will give me the parameter names?
我正在使用Microsoft SQL Server 2008.我有一个存储过程。我可以执行一个简单的查询,它会给我参数名称吗?
I have found this Link but it is not for Microsoft SQL Server 2008.
我找到了这个链接,但它不适用于Microsoft SQL Server 2008。
3 个解决方案
#1
7
To get names only you can use this query:
要获取名称,您可以使用此查询:
SELECT name
FROM sys.parameters
WHERE object_id = OBJECT_ID('YourProcedureName')
To get more detailed info (name, type and length of parameter):
要获取更详细的信息(参数的名称,类型和长度):
SELECT p.name AS ParameterName, t.name AS ParameterType, p.max_length AS ParameterLength
FROM sys.parameters AS p
JOIN sys.types AS t ON t.user_type_id = p.user_type_id
WHERE object_id = OBJECT_ID('YourProcedureName')
#2
2
On top of what Marek stated, you can also retrieve them programatically using the DeriveParameters method in the .NET library: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx
除了Marek所说的,你还可以使用.NET库中的DeriveParameters方法以编程方式检索它们:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx
#3
2
Check out my blog on database files and objects. http://craftydba.com/?p=2901
查看我的博客,了解数据库文件和对象。 http://craftydba.com/?p=2901
I have a stored procedure called SP_STORE_PRIMES in my sample [MATH] database.
我的示例[MATH]数据库中有一个名为SP_STORE_PRIMES的存储过程。
One way is to use the sys.parameters table. This can be optionally joined to types. Below is joined to sys.objects.
一种方法是使用sys.parameters表。这可以选择性地加入到类型中。下面加入了sys.objects。
-- Parameters to SP & FN
select o.name, p.* from sys.parameters p join sys.objects o
on p.object_id = o.object_id where is_ms_shipped = 0
go
A older system stored procedure is sp_sproc_columns.
较旧的系统存储过程是sp_sproc_columns。
-- Older system stored proc - show all parameters to one
sp_sproc_columns @procedure_name = 'SP_STORE_PRIMES'
go
Both ways will get you where you want to go.
两种方式都可以让你到达目的地。
#1
7
To get names only you can use this query:
要获取名称,您可以使用此查询:
SELECT name
FROM sys.parameters
WHERE object_id = OBJECT_ID('YourProcedureName')
To get more detailed info (name, type and length of parameter):
要获取更详细的信息(参数的名称,类型和长度):
SELECT p.name AS ParameterName, t.name AS ParameterType, p.max_length AS ParameterLength
FROM sys.parameters AS p
JOIN sys.types AS t ON t.user_type_id = p.user_type_id
WHERE object_id = OBJECT_ID('YourProcedureName')
#2
2
On top of what Marek stated, you can also retrieve them programatically using the DeriveParameters method in the .NET library: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx
除了Marek所说的,你还可以使用.NET库中的DeriveParameters方法以编程方式检索它们:http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder.deriveparameters.aspx
#3
2
Check out my blog on database files and objects. http://craftydba.com/?p=2901
查看我的博客,了解数据库文件和对象。 http://craftydba.com/?p=2901
I have a stored procedure called SP_STORE_PRIMES in my sample [MATH] database.
我的示例[MATH]数据库中有一个名为SP_STORE_PRIMES的存储过程。
One way is to use the sys.parameters table. This can be optionally joined to types. Below is joined to sys.objects.
一种方法是使用sys.parameters表。这可以选择性地加入到类型中。下面加入了sys.objects。
-- Parameters to SP & FN
select o.name, p.* from sys.parameters p join sys.objects o
on p.object_id = o.object_id where is_ms_shipped = 0
go
A older system stored procedure is sp_sproc_columns.
较旧的系统存储过程是sp_sproc_columns。
-- Older system stored proc - show all parameters to one
sp_sproc_columns @procedure_name = 'SP_STORE_PRIMES'
go
Both ways will get you where you want to go.
两种方式都可以让你到达目的地。