Retrieve DataTable info for a table in SQL Server

Question

I need to get the column names, primary keys, foreign keys, and other schema info. The DataTable class seem to contain all of those.

Below is the current code I got so far. With it I could retrieve all info except the foreign keys. I'm expecting them to be defined in DataTable.Constraints but they are not. This is my current code:

    private static DataTable LoadSchemaInfo(string tableName, SqlConnection connection)
    {
        string cmdText = "SELECT * FROM [" + tableName + "] WHERE 1 = 0";

        // Create a SqlDataAdapter to get the results as DataTable
        var sqlDataAdapter = new SqlDataAdapter(cmdText, connection);

        // Create a new DataTable
        var dataTable = new DataTable(tableName);

        // Fill the DataTable with the result of the SQL statement
        sqlDataAdapter.FillSchema(dataTable, SchemaType.Source);

        return dataTable;
    }

Any idea how to retrieve all info or how to get the FK (preferably without using the pure SQL syntax because I would then lack of some compile-time checking)?

Answer 1

If you're looking for the constraints of the schema of your database, then an ADO.net DataTable isn't your best option. You may find using SMO a more suitable option.

Answer 2

Using SMO you could do this...

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Smo.Agent;


// Add references: (in c:\Program Files\Microsoft SQL Server\90\SDK\Assemblies\)
// Microsoft SqlServer.ConnectionInfo
// Microsoft SqlServer.Management.Sdk.Sfc
// Microsoft SqlServer.Smo

namespace SMO
{
    class Program
    {
        static Database db;

        static void Main(string[] args)
        {
            Microsoft.SqlServer.Management.Smo.Server server;

            SqlConnection sqlConnection = new SqlConnection(@"Integrated Security=SSPI; Data Source=LOCAL");
            //build a "serverConnection" with the information of the "sqlConnection"
            Microsoft.SqlServer.Management.Common.ServerConnection serverConnection =
              new Microsoft.SqlServer.Management.Common.ServerConnection(sqlConnection);

            //The "serverConnection is used in the ctor of the Server.
            server = new Server(serverConnection);

            db = server.Databases["TestDB"];

            Table tbl;
            tbl = db.Tables["Sales"];
            foreach (ForeignKey fk in tbl.ForeignKeys)
            {
                Console.WriteLine("Foreign key {0} references table {1} and key {2}", fk.Name, fk.ReferencedTable, fk.ReferencedKey);
            } 
        }
    }
}

Answer 3

You could always inspect the sys catalog views in your database, using a simple ADO.NET query - views like:

• sys.columns with information about your columns
• sys.foreign_keys which stores information about foreign keys
• sys.tables for tables

etc. and so on. Just do a SELECT (list of fields) FROM sys.foreign_keys and see what you get!

See: Books Online Querying the SQL Server System Catalog for more details.

Answer 4

You can use SqlConnection to get the information.

string connectionString = "Data Source=.;Initial Catalog=Northwind;Integrated Security=True";
using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();

    DataTable dtAllForeignKeys = conn.GetSchema("ForeignKeys");

    string[] restrictionValues = { "Northwind", "dbo", "Orders" };
    DataTable dtForeignKeysForJustTheOrderTable = conn.GetSchema("ForeignKeys", restrictionValues);
    conn.Close();
}

Answer 5

The DataTable object is not the same as the database table. They just happen to have the same structure because of the way you create them. For instance, you could have a DataTable that contains a result of a join between several database tables. A DataTable could have a foreign key to another DataTable, but not to a database table.

To get the information about foreign keys in the database, you need to read metadata for the tables (when you call SELECT, you get only metadata for the query, that's why you get no information on keys).
You can either query this information directly from Information Schema Views or have SMO classes do that for you. In the latter case you start with Server object, then get Database, Tables and so on.