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

(Page 1 of 6 )

This article further digs into SQL Server 2000 with WMI together with the Visual Basic.NET language and VBScript. It explains various ways of retrieving information from the database.

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:

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\MicrosoftSQLServer")
Set colItems = objWMIService.ExecQuery( _
    "SELECT * FROM MSSQL_Column WHERE DatabaseName = 'AdventureWorks2000'",,48)
For Each objItem in colItems
    Wscript.Echo "Name: " & objItem.Name & ", TableName: " & objItem.TableName
Next

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

More MS SQL Server Articles
More By Jagadish Chaterjee

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

· hello guys...WMI with SQL Server has gone to further depth..have the article and...    · I want to connect to remote sql server by using vb.net code. I am using...   >>> Post your comment now!