Extract All Schema Information From Your Database

Introduction

This little code snippet shows how to build a DataSet with 10+ DataTables filled with details about your database.

Using the Code

All you need is an active DbConnection-Object and then call the following method:

using System.Data;
using System.Data.Common;


static DataSet getFullSchemaInfo(DbConnection connection)
{
    DataSet result = new DataSet("Schema Info");
    DataTable dt;

    if (connection.State == ConnectionState.Closed)
        connection.Open();

    dt = connection.GetSchema(DbMetaDataCollectionNames.MetaDataCollections);
    result.Tables.Add(dt);

    foreach (DataRow row in dt.Rows)
    {
        string collection = row[0].ToString();

        if (collection != DbMetaDataCollectionNames.MetaDataCollections)
        {
            try
            {
                DataTable dt2 = connection.GetSchema(collection);
                result.Tables.Add(dt2);
            }
            catch (Exception)
            {
                // some Data-Providers have problems when you grab some collections 
		// (e.g. "Indexes") without restrictions
                // hey ODBC, I'm looking at you!
                ;
            }
        }
    }
    return result;
}

The DataSet will contain at least the tables "MetaDataCollections", "DataSourceInformation", "DataTypes", "Restrictions" and "ReservedWords".

Typically, there will be also some more tables like "Columns", "Procedures", "ProcedureColumns", "Tables", "Views".

Just bind these tables to a DataGridView or any other data grid and you will can see this bunch of information.

History

• 2015-04-14 - Initial release