Reference for Columns returned by GetSchema for Sql Server

[pmayer995]

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

 

[RobinS]

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.

 

[pmayer995]

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

 

[RobinS]

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.
-----------------------------------------------