Hello,

The problem with GetSchema is that the columns vary by provider. In
fact, I can't even find a spec for the columns returned for Sql
Server! I don't know why Microsoft didn't even see fit to provide such
a spec, it really eliminates the usefulness of GetSchema.

Does anyone know of such a spec?

Thanks,
Phil

I'm not really sure what you mean. You can write a program to retrieve and
display them yourself. For example, here's a list of the columns you can
access through the GetSchema method of the datareader going against a
SQLServer database:

col name = ColumnName, type = System.String
col name = ColumnOrdinal, type = System.Int32
col name = ColumnSize, type = System.Int32
col name = NumericPrecision, type = System.Int16
col name = NumericScale, type = System.Int16
col name = IsUnique, type = System.Boolean
col name = IsKey, type = System.Boolean
col name = BaseServerName, type = System.String
col name = BaseCatalogName, type = System.String
col name = BaseColumnName, type = System.String
col name = BaseSchemaName, type = System.String
col name = BaseTableName, type = System.String
col name = DataType, type = System.Type
col name = AllowDBNull, type = System.Boolean
col name = ProviderType, type = System.Int32
col name = IsAliased, type = System.Boolean
col name = IsExpression, type = System.Boolean
col name = IsIdentity, type = System.Boolean
col name = IsAutoIncrement, type = System.Boolean
col name = IsRowVersion, type = System.Boolean
col name = IsHidden, type = System.Boolean
col name = IsLong, type = System.Boolean
col name = IsReadOnly, type = System.Boolean
col name = ProviderSpecificDataType, type = System.Type
col name = DataTypeName, type = System.String
col name = XmlSchemaCollectionDatabase, type = System.String
col name = XmlSchemaCollectionOwningSchema, type = System.String
col name = XmlSchemaCollectionName, type = System.String
col name = UdtAssemblyQualifiedName, type = System.String
col name = NonVersionedProviderType, type = System.Int32

Robin S.
------------------------------
<pmayer***@yahoo.com> wrote in message
Show quoteHide quote

Thanks. That's the spec for columns, but there are also tables,
indexes, foreign keys, etc. Where did you get that spec from?

Show quoteHide quote

I wrote a program to retrieve the information. tableName is passed in as a
String.

    Dim cn As New SqlConnection(My.Settings.DBConnString)
    'put the table name in brackets in case it has spaces in it
    Dim SQLString As String = "SELECT * FROM [" & tableName & "]"
    Try
        cn.Open()
        Dim cmd As New SqlCommand(SQLString, cn)
        Dim rdr As SqlDataReader =
cmd.ExecuteReader(CommandBehavior.KeyInfo)
        Dim tbl As DataTable = rdr.GetSchemaTable
        'This shows all of the information you can access about each
column.
        For Each col As DataColumn In tbl.Columns
            Debug.Print("col name = " & col.ColumnName & _
              ", type = " & col.DataType.ToString)
        Next
        For Each row As DataRow In tbl.Rows
            'DataTypeName actually gives the same
            '  data type name as is displayed in SQLServer
            Debug.Print("{0}, ColumnSize = {1}, DataType = {2},
DataTypeName = {3}, IsExpression = {4} ", _
              row("ColumnName"), row("ColumnSize"), row("DataType"), _
              row("DataTypeName"), row("IsExpression"))
        Next
        rdr.Close()
    Catch
        MessageBox.Show("Error opening the connection to the database.")
    Finally
        cn.Close()
    End Try

Here's how to get a list of tables in a SQLServer database:

    Public Sub New(ByVal connectionString As String)
        Dim cn As New SqlConnection(connectionString)
        Try
            cn.Open()
            'The Restrictions are: table_catalog, table_schema,
            ' table_name, table_type.
            'For my case, the table_catalog is the database name.
            'table_Schema is the owner.
            'Table_name is nothing because I want all tables.
            'table_type is "BASE TABLE".
            Dim restrictions() As String = New String() _
              {My.Settings.DatabaseName, "dbo", Nothing, "BASE TABLE"}
            Dim dt As DataTable = cn.GetSchema("Tables", restrictions)

            'Uncomment this if you want to see the columns you can
            ' access from the GetSchema command.
            'I'm leaving it in here for future reference.
            'For Each col As DataColumn In dt.Columns
            '    Debug.Print(col.ColumnName.ToString)
            'Next

            For Each rw As DataRow In dt.Rows

                'Uncomment this if you want to see the values
                '  for each of these columns.
                'I'm leaving it in here for future reference.
                'Debug.Print("Table_Catalog = {0}, Table_Schema = {1},
Table_Name = {2}, Table_Type = {3}", _
                '  rw.Item("TABLE_CATALOG"), rw.Item("TABLE_SCHEMA"), _
                '  rw.Item("TABLE_NAME"), rw.Item("TABLE_TYPE"))

                'sysdiagrams shows up if you have a database diagram;
exclude it here
                If rw.Item("TABLE_NAME").ToString.ToUpper <> "SYSDIAGRAMS"
Then
                    Me.Add(rw.Item("TABLE_NAME").ToString)
                End If
            Next

            'sort the list of tables
            Me.Sort()

        Catch
            MessageBox.Show("Error opening connection to database.")
        Finally
            cn.Close()
        End Try
    End Sub


I'm not sure about indexes and foreign keys. I'd have to muck around.

Robin S.
Ts'i mahnu uterna ot twan ot geifur hingts uto.
-----------------------------------------------
<pmayer***@yahoo.com> wrote in message
Show quoteHide quote

query question
autocomplete textbox like google
VS2003 versus VS2005 data adapters: Auto-generated update commands generate parameter errors?
How to cancel deleting a row in an event
Computed Columns