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

文章作者 100test 发表时间 2007:09:08 13:08:25
来源 100Test.Com百考试题网

　　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 (0select 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