列出SQLServer数据库中所有的存储过程

列出SQLServer数据库中所有的存储过程

Dim cn As rdoConnection

Dim ps As rdoPreparedStatement

Dim rs As rdoResultset

Dim strConnect As String

Dim strSQL As String

'利用 DSNless 连接到 pubs 数据库

'改变参数以适合你自己的 SQL Server

strConnect = "Driver={SQL Server}; Server=myserver; " & "Database=pubs; Uid=sa; Pwd="

Set cn = rdoEnvironments(0).OpenConnection(dsName:="", Prompt:=rdDriverNoPrompt, ReadOnly:=False, Connect:=strConnect) strSQL = "Select ,,,sc.length " & "FROM syscolumns sc,master..systypes st,sysobjects so " & "WHERE sc.id in (select id from sysobjects where type ='P')" & " AND so.type ='P' " & "AND sc.id = so.id " & "AND sc.type = st.type " & "AND sc.type <> 39"

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

Set rs = ps.OpenResultset(rdOpenStatic)

list1.AddItem "SP Name,Param Name,Data Type,Length"

While Not rs.EOF

list1.AddItem rs(0) & " , " & rs(1) & " , " & rs(2) & " , " & rs(3)

rs.MoveNext

Wend

rs.Close

Set rs = Nothing

cn.Close

Set cn = Nothing

【