Determine table structur

  • Thread starter Thread starter Markus Weber \(Megalith GmbH\)
  • Start date Start date
M

Markus Weber \(Megalith GmbH\)

Hello!

I just want to check with "ADO" and "SQLConnection" if a table exist and
what columns the table have. Does somebody know how to do that?

Thanks in Advance!
Markus
 
Markus said:
I just want to check with "ADO" and "SQLConnection" if a table exist
and what columns the table have. Does somebody know how to do that?
Click to expand...

I'm assuming you know how to execute queries using ADO.NET and interpret the
results in DataTables -- if not, take a look in MSDN or search in Google for
information on how to do this.

To find if a table exists in SQL Server, execute the following SQL:

\\\
select *
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'TableName'
and TABLE_TYPE = 'BASE TABLE'
///

(Replace 'TableName' with the name of the table you're looking for). If the
table exists, a row will be returned with details of the table; if it
doesn't exist, no data will be returned.

Once you've determined that the table exists, you can query the columns
within the table as follows:

\\\
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TableName'
order by ORDINAL_POSITION
///

This will give you lots of information about the table columns, including
their names, datatypes, max lengths, nullability, default values, etc.

HTH,
 
ADO.NET ? ADO ?

If you are using a SQL Server db you could for example query
Information_Schema.Tables and Information_Schema.Colmuns or do you have othe
requirements ?
 
I'm assuming you know how to execute queries using ADO.NET and interpret the
results in DataTables -- if not, take a look in MSDN or search in Google for
information on how to do this.

To find if a table exists in SQL Server, execute the following SQL:

\\\
select *
from INFORMATION_SCHEMA.TABLES
where TABLE_NAME = 'TableName'
and TABLE_TYPE = 'BASE TABLE'
///

(Replace 'TableName' with the name of the table you're looking for). If the
table exists, a row will be returned with details of the table; if it
doesn't exist, no data will be returned.

Once you've determined that the table exists, you can query the columns
within the table as follows:

\\\
select *
from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = 'TableName'
order by ORDINAL_POSITION
///

This will give you lots of information about the table columns, including
their names, datatypes, max lengths, nullability, default values, etc.

HTH,
Click to expand...

Bear in mind that the informationschema views in SQL Server limit the
result set by those databases the current user has access to.
 
Thanks for all your help! I did it, may be some one else can use it:

Dim objTable As DataTable
Dim arystrTable() As String
Dim intI As Integer
Dim strFields As String
strFields = ""
arystrTable = New String() {Nothing, Nothing, "TableToGetDataFrom",
Nothing}
objTable = objYourSQLConnection.GetSchema("Columns", arystrTable)
For intI = 0 To objTable.Rows.Count - 1
strFields = strFields & CStr(objTable.Rows(intI)(3)) & ";"
Next
 
Back
Top