如何確定儲存過程的輸入輸出值？

我有數百個 SP，我想知道：

• 帶類型的輸入參數
• 具有類型的輸出欄位（不是輸出參數）

當然，我可以手動瀏覽每一個並將其寫下來，但其中的樂趣在哪裡……不，從字面上看，其中的樂趣在哪裡:)

可以這樣做還是 Sql Management Studio 2008 R2 已經具有此功能？

我什至不知道從哪裡開始，所以任何答案都是可以接受的。

編輯以增強問題：如果我們將其視為 2 個不同的任務，我們能否更輕鬆地完成此任務。甚至在客戶端使用反射。（一個快速而骯髒的控制台應用程序就足夠了。）

您應該可以使用系統視圖INFORMATION_SCHEMA.PARAMETERS來做到這一點。你會在那裡得到你需要的東西。

它“為目前數據庫中目前使用者可以訪問的使用者定義函式或儲存過程的每個參數返回一行。對於函式，此視圖還返回一行帶有返回值資訊。”

PS：如果馬丁的問題的答案是“結果集中的列”，那麼忽略我的答案，它只是針對程序的參數，而不是針對任何結果。

對於輸入和輸出參數，您可以查看sys.parameters和。這是一個包含各種輸入/輸出參數、別名類型甚至 TVP（僅限 2008+）的程序：sys.proceduressys.types

USE [tempdb];
GO

CREATE TYPE dbo.TVPType AS TABLE(id INT);
GO

CREATE PROCEDURE dbo.foobar
   @a INT,
   @b SYSNAME = N'foo',
   @c DATETIME = NULL,
   @d dbo.TVPType READONLY,
   @e NVARCHAR(MAX),
   @f INT OUTPUT,
   @g INT = NULL OUTPUT
AS
BEGIN
   SET NOCOUNT ON;

   SELECT @f = COUNT(*) FROM sys.objects;
END
GO

這是一個從 和 中提取此資訊sys.parameters的sys.procedures查詢sys.types：

SELECT 
   s = QUOTENAME(OBJECT_SCHEMA_NAME(p.[object_id])),
   o = QUOTENAME(p.name),
   p = pr.name, 
   t1.name,
   pr.max_length, 
   pr.[precision], 
   pr.scale, 
   pr.is_output,
   pr.is_readonly
FROM sys.procedures AS p
INNER JOIN sys.parameters AS pr
ON p.[object_id] = pr.[object_id]
LEFT OUTER JOIN sys.types AS t1 
ON (pr.system_type_id = t1.system_type_id)
AND (pr.system_type_id <> t1.system_type_id
OR pr.user_type_id = t1.user_type_id)
WHERE p.[object_id] = OBJECT_ID(N'dbo.foobar')
ORDER BY pr.parameter_id;

結果：

s       o           p   name    max_length  [precision]  scale  is_output  is_readonly
-----   ----------  --- -------- ---------  -----------  -----  ---------  -----------
[dbo]   [foobar]    @a  int              4           10      0          0            0
[dbo]   [foobar]    @b  sysname        256            0      0          0            0
[dbo]   [foobar]    @c  datetime         8           23      3          0            0
[dbo]   [foobar]    @d  TVPType         -1            0      0          0            1
[dbo]   [foobar]    @e  nvarchar        -1            0      0          0            0
[dbo]   [foobar]    @f  int              4           10      0          1            0
[dbo]   [foobar]    @g  int              4           10      0          1            0

顯然，您需要進行一些按摩才能使這些值看起來像真實的數據類型。例如，將 nvarchar 的 max_length 減半，除非它是 -1，在這種情況下，它需要用MAX. 對於十進制、數字等，請適當添加精度和比例。如果您使用 TVP，您可能希望獲取SCHEMA_NAME用於這些類型的架構。等等等等。

不幸的是，除了暴力解析程序主體（即使沒有嚴格的 ninja RegEx 技能也不是微不足道的）來確定參數是否可以為空，或者它是否具有預設值以及預設值是什麼，沒有其他方法。此資訊根本不儲存在元數據中 - 雖然 中有has_default_value和default_value列，但對於 CLR 過程sys.parameters，它們只是非零NULL或非零。自 2006 年以來，我一直在抱怨這一點，但我們還沒有看到這方面的任何進展。我確實啟動了一個名為ParamParser的項目，它使用 PowerShell 來解析過程和函式體的參數、數據類型和預設值；隨意嘗試一下。這是上述過程的輸出：

對於結果集，Martin 是完全正確的——除了使用SET FMTONLY ON. 這個有一些特殊的錯誤，所以我會遠離它。一個流行的組合是OPENQUERY與“環回”連結伺服器一起使用，將結果選擇到 #temp 表中，但這僅在過程只有一個結果集時才有效。馬丁之前的一個答案中有一個很好的例子：

• 在 SQL Server 中創建可以保存儲存過程結果的臨時表的最簡單方法是什麼？

將輸出放入 #temp 表後，您可以執行：

EXEC tempdb.sys.sp_help N'#tablename';

除其他外，這將輸出列名及其數據類型的列表。雖然這對於數百個儲存過程來說是一個乏味的過程，但我確實有關於如何自動化它的想法，所以如果這對你來說似乎是一個有趣的解決方案，請告訴我，我可以擴展我的答案。

Martin 還提到了 SQL Server 2012 中的新功能，這將使這更容易（但它仍然僅限於儲存過程的第一個結果集）。您可以在我對上述相同問題的回答中看到更多詳細資訊。

對於返回值，我不知道有什麼方法可以從元數據中獲取這些值（無論如何它們都不應該用於數據）。

引用自：https://dba.stackexchange.com/questions/13893