以 RDO 列出 SQL Server 所有預存程序及其參數資訊

VB6 程式碼

Dim cn As rdoConnection
Dim ps As rdoPreparedStatement
Dim rs As rdoResultset
Dim strConnect As String
Dim strSQL As String

strConnect = "Driver={SQL Server}; Server=myserver; Database=pubs; Uid=sa; Pwd=;"
Set cn = rdoEnvironments(0).OpenConnection("", rdDriverNoPrompt, False, strConnect)

strSQL = "SELECT so.name AS SPName, sc.name AS ParamName, st.name AS DataType, sc.length AS Length " & _
         "FROM syscolumns sc " & _
         "INNER JOIN sysobjects so ON sc.id = so.id " & _
         "INNER JOIN systypes st ON sc.xtype = st.xusertype " & _
         "WHERE so.xtype = 'P' AND sc.xtype <> 39 " & _  -- 排除 timestamp
         "ORDER BY so.name, sc.colid"

Set ps = cn.CreatePreparedStatement("MyPs", strSQL)
Set rs = ps.OpenResultset(rdOpenStatic)

List1.AddItem "SP Name, Param Name, Data Type, Length"
Do While Not rs.EOF
    List1.AddItem rs(0) & ", " & rs(1) & ", " & rs(2) & ", " & rs(3)
    rs.MoveNext
Loop
rs.Close: Set rs = Nothing
cn.Close: Set cn = Nothing

查詢邏輯

• sysobjects.xtype = 'P' 篩選預存程序。
• syscolumns 記錄參數與資料行，colid=0 為傳回值，>=1 為輸入/輸出參數。
• Join systypes 取得型別名稱（st.name）與長度。

現代替代方案

SQL Server 2005+ 建議使用 sys.parameters、sys.procedures、sys.types 目錄檢視，語法更清晰、效能更佳。