Anders Hesselbom

Getting the meta data for a table from Visual Basic

2010-01-05

The last time I did this, I needed to build a custom Visual Basic entity generator. After two hours of coding, I had a tiny Windows Forms application that took some connection information and a table name, and returned an entity class with some properties and methods, and a collection class. With initialization, database write-back and all.

From the Management Studio, a call to the sp_help procedure and pass the name of the table you want to receive meta data about, like this:

EXEC sp_help 'dbo.spt_values'

(I still haven’t created any databases on the computer that I am writing this from, so I am grabbing the meta data from the spt_values table in the master database.

The procedure returns a few sets, and the second one contains a list of the table columns.

To call this from Visual Basic, you should know that the procedure is located in the sys namespace, and the parameter that it expects is called @objname. So, if you are using a dataset, the table with index 1 contains the column information. If you are using a data reader, you can call the NextResult function, like so (in a console application):

Using Cn As New SqlClient.SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=True")
    Cn.Open()
    Using Cmd As New SqlClient.SqlCommand("[sys].[sp_help]", Cn)
        Cmd.CommandType = CommandType.StoredProcedure
        Cmd.Parameters.AddWithValue("@objname", "dbo.spt_values")
        Dim R As SqlClient.SqlDataReader = Cmd.ExecuteReader()
        R.NextResult()
        Dim ColumnNameColumn As Integer = R.GetOrdinal("Column_name")
        Dim ColumnTypeColumn As Integer = R.GetOrdinal("Type")
        Dim ColumnLength As Integer = R.GetOrdinal("Length")
        While R.Read()
            Console.WriteLine(R.GetString(ColumnNameColumn))
            Console.WriteLine(R.GetString(ColumnTypeColumn))
            Console.WriteLine(R.GetInt32(ColumnLength).ToString())
        End While
        R.Close()
    End Using
    Cn.Close()
End Using

If you are using this to create something like a code generator, remember that Length column holds the column size in bytes. This means that a nvarchar (Unicode string columns) with the length set to 10, only can hold 5 characters.

The above code targets .NET Framework 3.5, but it would look exactly the same in the version above and below. It is written in Visual Basic 10 (VBx).

Categories: Visual Studio 10

Tags: Data access