Digging into SQL Server 2000 with WMI Using Visual Basic.NET and VBScript - How to retrieve “database file” information in SQL Server using WMI
(Page 2 of 6 )
Every piece of database information is generally stored in files, and may be further grouped with several other physical architecture scenarios. We can retrieve that information using WMI using the following code in VB.NET.
Try
Dim searcher As New ManagementObjectSearcher( _
"root\MicrosoftSQLServer", _
"SELECT * FROM MSSQL_DatabaseFile")
For Each queryObj As ManagementObject in searcher.Get()
Console.WriteLine("DatabaseName: {0}, Name: {1}, PhysicalName: {2}, ", queryObj("DatabaseName"), queryObj("Name"), queryObj("PhysicalName"))
Next
Catch err As ManagementException
MessageBox.Show("Error: " & err.Message)
End Try
The most important class note from the above code fragment is “MSSQL_DatabaseFile”, 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” (database file name), “Physical Name” (where it stays) 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_DatabaseFile",,48)
For Each objItem in colItems
Wscript.Echo "DatabaseName: " & objItem.DatabaseName & ", Name: " & objItem.Name & ", PhysicalName: " & objItem.PhysicalName
Next
Next: How to retrieve “view”, “stored procedure”, “user defined function”, and “trigger” information in SQL Server using WMI >>
More MS SQL Server Articles
More By Jagadish Chaterjee
/ 8 
