Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

Extract All Schema Information From Your Database

0.00/5 (No votes)
16 Apr 2015 1  
how to get as much information about your database as possible via ADO.NET

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:

C#
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

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here