Digging into SQL Server 2000 with WMI Using Visual Basic.NET and VBScript

I am not enclosing a downloadable solution for this as all the examples in this can be used by extending the downloadable solutions existing in my previous set of articles related to the same category.

How to retrieve “column” information in SQL Server database using WMI

Before reading this article, I strongly suggest that you go through my previous set of articles on SQL Server 2000 with WMI on ASP Free.  Those articles include installations, configurations, testing and other valuable information to seamlessly integrate with WMI.  As I covered all the introductory topics of WMI and other technical issues in my previous articles, I will not be repeating them in this article.

Now we will deal with the information/data/schema available in SQL Server 2000.  In my previous articles, I showed you how to retrieve database and table related information using WMI.  Now we shall dig further by retrieving all column information.  Let us go through the VB.NET script.

Try
    Dim searcher As New ManagementObjectSearcher( _
        "root\MicrosoftSQLServer", _
        "SELECT * FROM MSSQL_Column WHERE DatabaseName = 'AdventureWorks2000'")

    For Each queryObj As ManagementObject in searcher.Get()
         Console.WriteLine("Name: {0}, TableName: {1}", queryObj("Name"), queryObj("TableName"))
    Next
Catch err As ManagementException
    MessageBox.Show("Error: " & err.Message)
End Try

The most important class note from the above code fragment is “MSSQL_Column”, 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” (column name) and “TableName” (the name of the table to which the column belongs). 

The VBScript version of the same would be as follows:

How to retrieve “database file” information in SQL Server using WMI

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

How to retrieve “view”, “stored procedure”, “user defined function”, and “trigger” information in SQL Server using WMI

“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. 

How to retrieve all the parameters of “stored procedure” using WMI

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

How to add a primary key to a table using WMI

This aspect is a bit different from all of the above, but interesting. To add a new primary key to an existing table, we need to provide some of the parameters, such as server name, database name, table name, column name, name of the primary key, and so forth.  It would involve some input parameters to pass, along with invoking a WMI method.  Let us have the code first.

Try
        Dim classInstance As New ManagementClass( _
            "root\MicrosoftSQLServer", _
            "MSSQL_PrimaryKey", Nothing)

        Dim inParams As ManagementBaseObject = _
                    classInstance.GetMethodParameters("Create")

        inParams("Clustered") =  true
        inParams("ColumnName") =  "empno"
        inParams("DatabaseName") =  "employee"
        inParams("Name") =  "pk_emp_empno"
        inParams("SQLServerName") =  "(local)"
        inParams("TableName") =  "emp"

        Dim outParams As ManagementBaseObject = _
                    classInstance.InvokeMethod("Create", inParams, Nothing)
    Catch err As ManagementException
        MessageBox.Show("Error: " & err.Message)
    End Try

The most important class note from the above code fragment is “MSSQL_PrimaryKey”, which is a WMI class especially available for SQL Server 2000.  It has several properties to give us increasing amounts of information.  In this scenario, however, I am not retrieving any information.  Instead, I am trying to execute a method against the SQL Server database using WMI.

“inParams” (which is of type System.Managment.ManagmentBaseObject) is mainly used to pass parameters (input parameters) to the “Create” method (WMI method) dynamically.  Similarly, “outParams” generally contains the result of the method execution. 

In the above script, I worked with “InvokeMethod”, which is used to execute a WMI method dynamically. 

The VBScript version of the same would be as follows:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer")
Set objShare = objWMIService.Get("MSSQL_PrimaryKey")
Set objInParam = objShare.Methods_("Create"). _
    inParameters.SpawnInstance_()
objInParam.Properties_.Item("Clustered") =  true
objInParam.Properties_.Item("ColumnName") =  "empno"
objInParam.Properties_.Item("DatabaseName") =  "employee"
objInParam.Properties_.Item("Name") =  "pk_emp_empno"
objInParam.Properties_.Item("SQLServerName") =  "(local)"
objInParam.Properties_.Item("TableName") =  "emp"
Set objOutParams = objWMIService.ExecMethod("MSSQL_PrimaryKey", "Create", objInParam)

Can we rename a database using WMI?

You can even rename an entire database using WMI.  And it is perfectly easy to do as well. Please take care! This script may stop all of your other applications working on the database. Use the script with caution.  Let us have the code first.

Try

        Dim classInstance As New ManagementObject( _
                    "root\MicrosoftSQLServer", _
                    "MSSQL_Database.Name='eAttendance',SQLServerName='(LOCAL)'", _
                    Nothing)

        Dim inParams As ManagementBaseObject = _
                    classInstance.GetMethodParameters("Rename")

        inParams("Name") =  "eAttendance2"

        Dim outParams As ManagementBaseObject = _
                    classInstance.InvokeMethod("Rename", inParams, Nothing)
    Catch err As ManagementException
        MessageBox.Show("Error: " & err.Message)
    End Try

“inParams” (which is of type System.Managment.ManagmentBaseObject) is mainly used to pass parameters (input parameters) to the “Rename” method (WMI method) dynamically. Similarly, “outParams” generally contains the result of the method execution. 

In the above script, I worked with “InvokeMethod”, which is used to execute a WMI method dynamically. 

The VBScript version of the same would be as follows:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer &
"\root\MicrosoftSQLServer")
Set objShare = objWMIService.Get
("MSSQL_Database.Name='eAttendance',SQLServerName='(LOCAL)'")

Set objInParam = objShare.Methods_("Rename"). _
    inParameters.SpawnInstance_()

objInParam.Properties_.Item("Name") =  "eAttendance2"

Set objOutParams = objWMIService.ExecMethod
("MSSQL_Database.Name='eAttendance',SQLServerName='(LOCAL)'",
"Rename", objInParam)

Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.