Digging into SQL Server 2000 with WMI Using Visual Basic.NET and VBScript - How to retrieve “view”, “stored procedure”, “user defined function”, and “trigger” information in SQL Server using WMI
(Page 3 of 6 )
“View” is something like a logical and virtual table. Every view gets accompanied with a SELECT query. We can even retrieve that information using WMI, by using the following code in VB.NET.
Try
Dim searcher As New ManagementObjectSearcher( _
"root\MicrosoftSQLServer", _
"SELECT * FROM MSSQL_View")
For Each queryObj As ManagementObject in searcher.Get()
Console.WriteLine("DatabaseName: {0}", queryObj("DatabaseName"))
Console.WriteLine("Name: {0}", queryObj("Name"))
Console.WriteLine("Text: {0}", queryObj("Text"))
Console.WriteLine("")
Next
Catch err As ManagementException
MessageBox.Show("Error: " & err.Message)
End Try
The most important class note from the above code fragment is “MSSQL_View”, 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” (view name), “Text” (the SELECT query for the view) 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_View",,48)
For Each objItem in colItems
Wscript.Echo "DatabaseName: " & objItem.DatabaseName
Wscript.Echo "Name: " & objItem.Name
Wscript.Echo "Text: " & objItem.Text
Next
If you replace “MSSQL_View” with “MSSQL_StoredProcedure” in the above two code fragments, you will get the list of all “StoredProcedures” complete with the related coding. Similarly, you can use “MSSQL_UserDefinedFunction” to get the list of all “stored user defined function” available in the database along with coding. You can also use “MSSQL_trigger” to get the list of all “triggers” available in the database along with coding.
Next: How to retrieve all the parameters of “stored procedure” using WMI >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 9 
