retrieve default values

  • Thread starter Thread starter Kath
  • Start date Start date
K

Kath

Hi,

I am trying to export info about a SQL server database in vb.net. I
need to retrieve the following info about the fields in the tables:
names, data type, length and default values.

I have used the following code to retrieve the first three properies:

Dim strSQL As String = "SELECT * FROM " +
CStr(DBDS.Tables(0).Rows(count)("TABLE_NAME"))
Dim aReader As OleDbDataReader
Dim oledbCmd As OleDbCommand = New OleDbCommand(strSQL, oleDBConn)
aReader = oledbCmd.ExecuteReader()
Dim schemaTable As DataTable = aReader.GetSchemaTable()
Dim ColumnName As String =
schemaTable.Rows(0)("ColumnName").ToString()
Dim Length As String = schemaTable.Rows(0)("ColumnSize").ToString()
Dim DataType As String = schemaTable.Rows(0)("DataType").ToString()
aReader.Close()

However, I cannot find a way to find the default value for each field.
Does anyone know how to do this? I have searched the newsgroups and
can't find an answer - some people talk about using ADOX, but
apparently this doesn't work with .NET? Any help, even just to be told
it's not possible, would be appreciated!!

Thanks,
Kath
 
Hi Kath,

I have not tried this way but you could try to call FillSchema method of the
DataAdapter. It suppose to fill in schema for the table. Then try to check
DefaultValue property of the dataColumn. It could work could
not. Another way is to query system tables directly in a SQL Server. They
keep all this staff fro sure
 
The default value is stored in the syscomments table in the text column.
That's the name of the column, "text".
join from the syscolumns to the syscomments as follows

syscolumns.cdefault = syscomments.id
 
Back
Top