SQL Server Metadata

Download the code for this article: AdvancedBasics0311.exe (135KB)

Q Frequently when I am working on a project, I must find the name of a particular field in a table in SQL Server™ 2000. Other times I must find the exact name of a field and table in which it is located. Still other times I need to get the datatype of a particular field. How can I accomplish these tasks easily?

A When I've needed the name of a field or a field's datatype, I've often opened the table I thought it was in and gone poking around. This process is not very efficient because the fields in a table are not in alphabetical order and as a result it's not easy to find a field, especially when the table has a large number of fields. After many rounds of this kind of trial and error searching, it dawned on me—why not build a tool that does the searching? That tool is the subject of this month's column.

The first version of the tool's interface is shown in Figure 1, which shows where I search for a field called Region in all tables in the Northwind sample database. Version 1 of the tool allows you to search for a field in either all tables or a specific table. You can also search for part of the field name (the default) or look for an exact match by unchecking the Contains box. This allows you to vary the search, as the output shows, to look for a word in a field name or find an exact field.

Figure 1 SQL Server Search Tool

Let's see how this tool was constructed. The first bit of code for the tool, shown in Figure 2, is used to execute the required SQL. I also added the following global variables at the form level:

Dim localServer, localDB, localUser, localPassword As String
Dim localConnectionString As String
Dim dsTables As DataSet

To get access to the database, you must provide the user with a way to select the database they want to query. The form named frmDBInfo asks the user for the database login information. To display the form, frmSelectDB calls the SelectDBandLoadTables routine shown in Figure 3. This routine simply displays the form and retrieves the information from the user. Then to actually retrieve the tables, it calls the GetTableNames routine, shown here:

Function GetTableNames() _
    As DataSet
    Dim SQL As String
    Dim ds As DataSet

The real work is done in the select statement:

SQL = "SELECT Name FROM sysobjects "
SQL &= "WHERE (type = 'U') AND (status > 0) " 
SQL &= "order by Name"

This statement pulls the table names from the sysobjects table in the selected database (later I'll look at another way to do this). There is a tremendous amount of useful data in sysobjects and other system tables that can be extracted for your own purposes. Executing this SQL statement yields a list of all the tables which are returned in a DataSet:

  ds = RunSQLWithDataSet(SQL)
  Return ds

The DataSet's table(0) is then loaded into the combobox. The table is also retained in the dsTables DataSet for later use.

Now, let's turn to another routine that I'll need to complete the search: GetTableDetails (see Figure 4). This routine uses the SQL query shown here to retrieve the details of a particular table, including field names, datatype, and maximum length:

 SQL = "select '" & TableName & "' as TableName, " _
    SQL &= "COLUMN_NAME, DATA_TYPE, " _
    SQL &= "CHARACTER_MAXIMUM_LENGTH  from " _
    SQL &= "INFORMATION_SCHEMA.COLUMNS where " _
    SQL &= "table_name = '" & TableName & "' "

GetTableDetails is called when the user clicks the Search button (called cmdSearch). More on this soon.

One more function that I need to look at is CheckColumnIdentity.

Function CheckColumnIdentity(ByVal TableName As String, _
    ByVal ColumnName As String) As Boolean
    Dim SQL As String
    SQL = "SELECT COLUMNPROPERTY( OBJECT_ID('" & " _
    SQL &= "TableName & "'),'" & ColumnName & _
    SQL &= "','IsIdentity')"
    If CInt(RunSQLScalar(SQL)) = 1 Then
        Return True
    Else
        Return False
    End If
End Function

This function takes a table name and column name as parameters and returns a Boolean value, indicating whether the column is an identity column. It uses COLUMNPROPERTY to determine if the column is an identity column. You can also use this property to determine if a column allows nulls, and so on.

Now, let's put this together by looking at the cmdSearch Click event code. The first few lines of code define the variables I'll need:

Dim dt, dttemp As DataTable
Dim ds As New DataSet
Dim tabletosearch As String
Dim rw As DataRow
Dim IsMatch As Boolean = False

The following two lines set tabletosearch to the name of the table to search and clears the output textbox:

tabletosearch = cboTables.Text
txtOutput.ResetText()

The next few lines control how the search is handled. If chkAllTables is True, then all of the details for each table are loaded into the dt table. If chkAllTables is False, then dt is loaded with the details for only the selected table:

If chkAllTables.Checked Then
    For Each rw In dsTables.Tables(0).Rows
        dttemp = GetTableDetails(rw("Name").ToString)
        ds.Merge(dttemp)
    Next
    dt = ds.Tables(0)
Else
    dt = GetTableDetails(tabletosearch)
End If

Now that the tables are loaded, I can perform the search. The search is handled inside the For Each loop, which moves through all rows in the dt table:

For Each rw In dt.Rows

The next line sets IsMatch to False as the default for each field. IsMatch is a flag that determines whether to output the field:

IsMatch = False

Next, if chkContains is True, then InStr is used to determine if a match exists. If chkContains is False, then the Else clause checks for an exact match. If a match is found, IsMatch is set to True:

If chkContains.Checked Then
    If InStr(UCase(rw("ColumnName").ToString), _
        UCase(txtSearchField.Text)) > 0 Then
        IsMatch = True
    End If
Else
    If UCase(rw("ColumnName").ToString) = UCase(txtSearchField.Text) Then
        IsMatch = True
    End If
End If

Now that the test is complete, I can output the data if IsMatch is True. The values in the various fields in the datatable are output to txtOutput, as shown here:

If IsMatch Then
    txtOutput.Text &= rw("TableName").ToString _
        & " : " & rw("ColumnName").ToString & " - "
    txtOutput.Text &= rw("DataType").ToString _
        & " (" & rw("Length").ToString & ")"

    If CBool(rw("Identity").ToString) Then
        txtOutput.Text &= " Identity"
End If
        txtOutput.Text &= vbCrLf
    End If
Next

That's it. There is not a lot of code to this tool, but it sure is handy.

The information schema views provided by SQL Server 2000 are used to retrieve some of the metadata in this application. These views provide a wealth of metadata about SQL Server databases. Just for fun, I tried using a few different views and found some really cool features. The following SQL statement pulls a list of the tables and views in the database, sorted by table name:

select *
from INFORMATION_SCHEMA.Tables order by table_type, table_name

This SQL query pulls information about the stored procedures in the database, as shown in the following:

select *
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'

And if that wasn't cool enough, check this out. The following code pulls the names of all the stored procedures and the code to create them:

select routine_name, routine_definition
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE'

The first three rows returned for the Northwind sample database are shown in Figure 5.

Now, let's brainstorm a minute. What can I do with this code? I can put it into my search routine and use InStr to search for particular fields. This would allow me to search not only tables but also stored procedures for the fields. Of course, I could add Views rather easily as well.

One change I would make to the SQL code is shown here:

select routine_name, routine_definition
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE='PROCEDURE' and left(routine_name,3) <> 'dt_'
order by routine_name

This version filters out the dt_ procedures system procedures. It also sorts the output by procedure name. You should note that the order by clause here will not work if you run this query against Master db because group by and order by can't handle rows longer than 8060 bytes.

There are quite a few ways you can query a table about itself. I have only scratched the surface of the possibilities here.

Send your questions and comments for Ken to  basics@microsoft.com.

---

Ken Spencer works for 32X Tech (http://www.32X.com), where he provides training, software development, and consulting services on Microsoft technologies.

---