Digging into SQL Server 2000 with WMI Using Visual Basic.NET and VBScript - How to retrieve all the parameters of “stored procedure” using WMI
(Page 4 of 6 )
Some of the stored procedures in the database may have parameters. Those parameters would be either IN or OUT or IN OUT type of parameters. IN stands for INPUT parameter, and OUTPUT stands for OUTPUT parameter. We can retrieve that information using WMI with the following code in VB.NET.
Try
Dim searcher As New ManagementObjectSearcher( _
"root\MicrosoftSQLServer", _
"SELECT * FROM MSSQL_StoredProcedureParameter")
For Each queryObj As ManagementObject in searcher.Get()
Console.WriteLine("-------------------")
Console.WriteLine("DatabaseName: {0}", queryObj("DatabaseName"))
Console.WriteLine("StoredProcedureName: {0}", queryObj("StoredProcedureName"))
Console.WriteLine("Name: {0}", queryObj("Name"))
Console.WriteLine("Output: {0}", queryObj("Output"))
Next
Catch err As ManagementException
MessageBox.Show("Error: " & err.Message)
End Try
The most important class note from the above code fragment is “MSSQL_StoredProcedureParameter”, which is a WMI class especially available for SQL Server 2000. It has several properties to give us increasing amounts of information. At the moment, I chose only “name” (stored procedure parameter name), “StoredProcedureName” (name of the stored procedure to which it belongs), “output” (true or false, which denotes whether or not it is an output parameter) and “DatabaseName” (to which database it belongs).
The VBScript version of the same would be as follows:
strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer")
Set colItems = objWMIService.ExecQuery( _
"SELECT * FROM MSSQL_StoredProcedureParameter",,48)
For Each objItem in colItems
Wscript.Echo "----------------------"
Wscript.Echo "DatabaseName: " & objItem.DatabaseName
Wscript.Echo "StoredProcedureName: " & objItem.StoredProcedureName
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "Output: " & objItem.Output
Next
Next: How to add a primary key to a table using WMI >>
More MS SQL Server Articles
More By Jagadish Chaterjee
