I am using GetSchemaTable to do some grunt work and I've come across a situation that makes me believe there is an error in the implementation.

In a routine that generates stored procedures for tables, I used Row("IsKey") to determine which column was the Primary Key. Later on I decided to generate procedures for views as well as tables. I found some very interesting behavior:

When processing a table, IsKey works as expected.

When processing a view , IsKey evaluates to True for each column in the view that is a primary key for the base table. This is probably by design.

When processing a table, IsUnique *does not* evaluate to True for the primary key of the table and *does not* evalutate to True for columns with a UNIQUE constraint. Basically, IsUnique appears to be completely useless.

Here is the code:

        Dim ProcedureWriter As New StringWriter()

  ' Create a SELECT stored procedure to get the table schema from the reader
        With ProcedureWriter
            .GetStringBuilder.Length = 0
            .WriteLine("CREATE PROCEDURE " & TableName & "Select")
            .WriteLine("     AS")
            .WriteLine("SET NOCOUNT ON;")
            .WriteLine("     SELECT * FROM [" & TableName & "]")
        End With

        ' Drop and re-create the stored procedure inside of a transaction
        DropAndCreateStoredProcedure(TMSConnection, TableName & "Select", _
 ProcedureWriter.ToString())

        ' Now retrieve the table schema with TableReader.GetTableSchema()
        Dim SchemaCommand As New SqlCommand()
        With SchemaCommand
            .Connection = TMSConnection
            .CommandText = TableName & "Select"
            .CommandType = CommandType.StoredProcedure
        End With
        Dim SchemaReader As SqlDataReader
        SchemaCommand.Connection.Open()
        SchemaReader = SchemaCommand.ExecuteReader(CommandBehavior.KeyInfo _
+ CommandBehavior.SchemaOnly)
        Dim TableSchema As DataTable = SchemaReader.GetSchemaTable()
        SchemaReader.Close()
        SchemaCommand.Connection.Close()

        ' Determine if this table has a primary key.
        Dim HasPrimaryKey As Boolean = False
        Dim tempRow As DataRow
        For Each tempRow In TableSchema.Rows
            If (tempRow("IsKey") And tempRow("IsUnique")) Then
                HasPrimaryKey = True
                Exit For
            End If
        Next

For those who aren't familiar with GetSchemaTable(), here is what my code does:

First, it creates a stored procedure with the table name and "Select" appended to it, e.g. "CompanySelect" for the Company table. Then it uses the newly created stored procedure to open the reader.

At this point, the data from the SELECT statement is not important, only that the reader has something to use to retreive the schema.

GetSchemaTable returns a DataTable to play with. This DataTable is *not* a clone of the base table. Instead of defining columns that match the base table, it puts schema information into the rows.

Finally, the code iterates through the rows in the table to inspect the schema information.

Each row of the table has the following columns:

ColumnName
ColumnSize
IsKey
IsUnique
DataType
ProviderType
...

Here is the relevant documentation:

IsUnique
True: No two rows in the base table-the table returned in BaseTableName-can have the same value in this column. IsUnique is guaranteed to be true if the column constitutes a key by itself or if there is a constraint of type UNIQUE that applies only to this column.
False: The column can contain duplicate values in the base table. The default of this column is false.

IsKey
True: The column is one of a set of columns in the rowset that, taken together, uniquely identify the row. The set of columns with IsKey set to true must uniquely identify a row in the rowset. There is no requirement that this set of columns is a minimal set of columns. This set of columns may be generated from a base table primary key, a unique constraint or a unique index.
False: The column is not required to uniquely identify the row.

I've tried all combinations of the CommandBehaviors for ExecuteReader and the closest results come from KeyInfo + SchemaOnly.

I was depending on IsKey and IsUnique to help me identify whether the base table is a real table or a view, and also to help me identify which columns are candidate keys. It now seems that I will have to query the SQL system tables to get that information. Very sucky indeed!