SQL Server table Schema

[JPS]

Does anyone know how to query a table and get the schema
informationback as well as the values in the fields. Here is what I
have done so far. I cannot get both.

using (SqlConnection conF = new
SqlConnection ( "server=" + srvr +
";Integrated Security=SSPI;database=" + dbName +
";Initial Catalog=" + tblNames[tbl].ToString
( )))
{
conF.Open ( );
SqlCommand sqlCmd = new SqlCommand ( );

sqlCmd = conF.CreateCommand ( );

sqlCmd.CommandText = "SELECT * FROM
INFORMATION_SCHEMA.COLUMNS";
SqlDataReader sqlDr = sqlCmd.ExecuteReader ( );
int fc = 0;
if (sqlDr.FieldCount > 0)
{
while (sqlDr.Read ( ))
{
fc++;
for (int h = 0; h <= sqlDr.FieldCount - 1;
h++)
{
Debug.WriteLine ( sqlDr[h].ToString ( )
);
}

//foreach (DataRow dr in dt.Rows)
//{
// //Debug.WriteLine ( dr[x] );
// foreach (DataColumn dc in dt.Columns)
// {
// Debug.WriteLine ( dc.ColumnName +
" Value: " + sqlDr[x].ToString() );
// x++;
// }
//}
}
}
conF.Close ( );

 

[Marc Scheuner]

Does anyone know how to query a table and get the schema

informationback as well as the values in the fields. Here is what I
have done so far. I cannot get both.

That's correct - you cannot get both at the same time - they're very
different things indeed.

Schema information for a table (info about the columns) is a once per
table thing and it's the same for all row - that's what we have come
to except, right? This information is stored in the database's data
dictionary (or whatever it might be called today).

Data is a per-row thing - a table can have many rows - and that data
is stored in the table itself - that's the whole point of it.

So I don't see how you could get both types of information in the same
call - it's just not possible. Why would you even need this??

Marc

 

[ForrestPhoto]

So I don't see how you could get both types of information in the same

call - it's just not possible. Why would you even need this??

I think DataReaders have a method called GetSchemaTable() that returns
the table schema while you read the data. You could use this to figure
out how to deal with the data records, if you don't know what types to
expect.

 

[Guest]

I tried Information_schema.Columns. CHARACTER_MAXIMUM_LENGTH and
CHARACTER_OCTET_LENGTH only work for char and varchar.

How can I get text, image and integer type length?

They are meaningless/fixed for those data types.

Does default asp.net account in SQL Server has default right to visit
Information_Schema and sp_help?

Look it up.

Arne