发表评论(0)作者：, 平台：, 阅读：10914, 日期：2000-03-12

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



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

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 so.name,sc.name,st.name,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