Ado.net Getting the schema for a table

Question

Given an SQLConnection object how can you get a schema for a single table?

I was trying this the other day and I seemed to be able to get the schema from a DataSet which I'd gotten from running a query, but all the schema info I could get from the connection seemed to be related to what tables were available and not the actual details on the tables.

I'm sure there is a simple way to do this.

Answer 1

I think accessing the schema from the query (via GetSchemaTable) is the only way to do it. You can run a query which returns no rows (select * from table where 1=2) if the schema is all you're interested in.

You should use the KeyInfo CommandBehaviour to execute the source query, as otherwise not all the information returned is guaranteed to be accurate

Command.ExecuteReader(CommandBehavior.KeyInfo)

Answer 2

This code will do what you want (obviously change the table name, server name etc):

using System;
using System.Collections.Generic;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace ConsoleApp
{
    class Program
    {
        static void Main(string[] args)
        {
            string query = "SELECT * FROM t where 1=0";
            string connectionString = "initial catalog=test;data source=localhost;Trusted_Connection=Yes";

            DataTable tblSchema;

            using (SqlConnection cnn = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = query;
                    cmd.CommandType = CommandType.Text;
                    cnn.Open();
                    using (SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo))
                    {
                        tblSchema = rdr.GetSchemaTable();
                    }
                    cnn.Close();
                }
            }
            int numColumns = tblSchema.Columns.Count;
            foreach (DataRow dr in tblSchema.Rows)
            {
                Console.WriteLine("{0}: {1}", dr["ColumnName"], dr["DataType"]);
            }

            Console.ReadLine();
        }
    }
}

Answer 3

SQL Server - query the catalog views... sysobjects, syscolumns etc if SQL 2000 or earlier... sys.objects, sys.columns etc if SQL 2005 or higher. (although the older views are still available it is advisable to use the newer ones)

Complete reference here: http://msdn.microsoft.com/en-us/library/ms189783.aspx

Example:

select so.name, sc.*
from sys.objects as so
inner join sys.columns as sc on sc.object_id = so.object_id
where so.name='some_table'