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)?

Solution 1

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);
            } 
        }
    }
}

Solution 2

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.

Comments

[Wernight]

Frankly horrible to use. Not OO and returns ID of tables when I need table names.

[Wernight]

Nice. Any version of that code using a SqlConnection? I hope the external reference won't be a trouble.

[marc_s]

Horrible - that's up for discussion. But efficient!! Also:j ust use OBJECT_NAME(object_id) and you got your table name.... also: SQL Server is a RDBMS - not an OO system.....

[Wernight]

Thanks for that OBJECT_NAME, it's very helpful.

[Wernight]

It seem not to contain directly the name of the referenced table. Only the name of the FK constraint.

[VladV]

It's usually more convenient to use INFORMATION_SCHEMA views instead of catalog views.

[VladV]

This should work: new Server(new ServerConnection(sqlConnection)).

[Wernight]

Thanks. I wish to accept your answer also, as I used it. SMO sadly requires third-party deployment. SMO is however closer to the ideal solution.

[Tom Brothers]

blog.sqlauthority.com/2006/11/01/…

[marc_s]

@VladV: I would say it's more interoperable to use INFORMATION_SCHEMA - but the information contained in those views is nothing compared to the sys views....