Discover Tables and Columns

These scenarios demonstrate how to list the tables and columns in an unknown database.

Scenario Prototype

public interface IDiscoverTablesAndColumnsScenario
{
    IList<string> ListColumnsInTable(string schemaName, string tableName);

    IList<string> ListColumnsInView(string schemaName, string viewName);

    IList<string> ListTables();

    IList<string> ListViews();
}

ADO.NET

The SQL needed to list tables, views, and columns is database-specific.

Warning!

While most databases expose Information Schema (e.g. INFORMATION_SCHEMA.TABLES), the column names may vary from vendor to vendor.

public class DiscoverTablesAndColumnsScenario : SqlServerScenarioBase, IDiscoverTablesAndColumnsScenario
{
    public DiscoverTablesAndColumnsScenario(string connectionString) : base(connectionString)
    { }

    public IList<string> ListColumnsInTable(string schemaName, string tableName)
    {
        const string sql =
            @"SELECT c.name FROM sys.columns c
                INNER JOIN sys.tables t ON c.object_id = t.object_id
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND t.name = @TableName";

        var result = new List<string>();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@SchemaName", schemaName);
            cmd.Parameters.AddWithValue("@TableName", tableName);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }

    public IList<string> ListColumnsInView(string schemaName, string viewName)
    {
        const string sql =
            @"SELECT c.name FROM sys.columns c
                INNER JOIN sys.views v ON c.object_id = v.object_id
                INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND v.name = @ViewName";

        var result = new List<string>();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            cmd.Parameters.AddWithValue("@SchemaName", schemaName);
            cmd.Parameters.AddWithValue("@ViewName", viewName);
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }

    public IList<string> ListTables()
    {
        const string sql =
            "SELECT s.name + '.' + t.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id";

        var result = new List<string>();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }

    public IList<string> ListViews()
    {
        const string sql =
            "SELECT s.name + '.' + v.name  FROM sys.views v INNER JOIN sys.schemas s ON v.schema_id = s.schema_id";

        var result = new List<string>();
        using (var con = OpenConnection())
        using (var cmd = new SqlCommand(sql, con))
        {
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                    result.Add(reader.GetString(0));
            }
        }
        return result;
    }
}

Chain

Chain exposes information about the database via the DatabaseMetadata property. If you don't use the Preload method, only tables and views previously seen will be available.

public class DiscoverTablesAndColumnsScenario : IDiscoverTablesAndColumnsScenario
{
    readonly SqlServerDataSource m_DataSource;

    public DiscoverTablesAndColumnsScenario(SqlServerDataSource dataSource)
    {
        m_DataSource = dataSource;
    }

    public IList<string> ListColumnsInTable(string schemaName, string tableName)
    {
        return m_DataSource.DatabaseMetadata.GetTableOrView(schemaName + "." + tableName)
            .Columns.Select(c => c.SqlName).ToList();
    }

    public IList<string> ListColumnsInView(string schemaName, string viewName)
    {
        return m_DataSource.DatabaseMetadata.GetTableOrView(schemaName + "." + viewName)
            .Columns.Select(c => c.SqlName).ToList();
    }

    public IList<string> ListTables()
    {
        m_DataSource.DatabaseMetadata.PreloadTables(); //Only need to call this once
        return m_DataSource.DatabaseMetadata.GetTablesAndViews().Where(t => t.IsTable)
            .Select(t => t.Name.ToString()).ToList();
    }

    public IList<string> ListViews()
    {
        m_DataSource.DatabaseMetadata.PreloadViews(); //Only need to call this once
        return m_DataSource.DatabaseMetadata.GetTablesAndViews().Where(t => t.IsTable == false)
            .Select(t => t.Name.ToString()).ToList();
    }
}

Dapper

The SQL needed to list tables, views, and columns is database-specific. See the ADO.NET example above for links to the documentation. 

public class DiscoverTablesAndColumnsScenario : ScenarioBase, IDiscoverTablesAndColumnsScenario
{
    public DiscoverTablesAndColumnsScenario(string connectionString) : base(connectionString)
    { }

    public IList<string> ListColumnsInTable(string schemaName, string tableName)
    {
        const string sql =
            @"SELECT c.name FROM sys.columns c
                INNER JOIN sys.tables t ON c.object_id = t.object_id
                INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND t.name = @TableName";

        using (var con = OpenConnection())
            return con.Query<string>(sql, new { schemaName, tableName }).ToList();
    }

    public IList<string> ListColumnsInView(string schemaName, string viewName)
    {
        const string sql =
            @"SELECT c.name FROM sys.columns c
                INNER JOIN sys.views v ON c.object_id = v.object_id
                INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
                WHERE s.name = @SchemaName AND v.name = @ViewName";

        using (var con = OpenConnection())
            return con.Query<string>(sql, new { schemaName, viewName }).ToList();
    }

    public IList<string> ListTables()
    {
        const string sql =
            "SELECT s.name + '.' + t.name FROM sys.tables t INNER JOIN sys.schemas s ON t.schema_id = s.schema_id";

        using (var con = OpenConnection())
            return con.Query<string>(sql).ToList();
    }

    public IList<string> ListViews()
    {
        const string sql =
            "SELECT s.name + '.' + v.name  FROM sys.views v INNER JOIN sys.schemas s ON v.schema_id = s.schema_id";

        using (var con = OpenConnection())
            return con.Query<string>(sql).ToList();
    }
}

DbConnector

TODO

Entity Framework 6

TODO

Entity Framework Core

The SQL needed to list tables, views, and columns is database-specific. See the ADO.NET example above for links to the documentation.

public class DiscoverTablesAndColumnsScenario : IDiscoverTablesAndColumnsScenario
{
    private readonly Func<OrmCookbookContext> CreateDbContext;

    public DiscoverTablesAndColumnsScenario(Func<OrmCookbookContext> dBContextFactory)
    {
        CreateDbContext = dBContextFactory;
    }

    public IList<string> ListColumnsInTable(string schemaName, string tableName)
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @"SELECT c.name AS ColumnName
            FROM sys.columns c
            INNER JOIN sys.tables t ON c.object_id = t.object_id
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
            WHERE s.name = @SchemaName AND t.name = @TableName";
        commnd.CommandText = sql;
        commnd.Parameters.Add(new SqlParameter { ParameterName = "SchemaName", DbType = DbType.String, Value = schemaName });
        commnd.Parameters.Add(new SqlParameter { ParameterName = "TableName", DbType = DbType.String, Value = tableName });
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var columnNames = new List<string>();
        while (reader.Read())
        {
            int columnNameOrdinal = reader.GetOrdinal("ColumnName");
            string columnName = reader.GetString(columnNameOrdinal);
            columnNames.Add(columnName);
        }
        return columnNames;
    }

    public IList<string> ListColumnsInView(string schemaName, string viewName)
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @"SELECT c.name AS ColumnName
            FROM sys.columns c
            INNER JOIN sys.views v ON c.object_id = v.object_id
            INNER JOIN sys.schemas s ON v.schema_id = s.schema_id
            WHERE s.name = @SchemaName AND v.name = @ViewName";
        commnd.CommandText = sql;
        commnd.Parameters.Add(new SqlParameter { ParameterName = "SchemaName", DbType = DbType.String, Value = schemaName });
        commnd.Parameters.Add(new SqlParameter { ParameterName = "ViewName", DbType = DbType.String, Value = viewName });
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var columnNames = new List<string>();
        while (reader.Read())
        {
            int columnNameOrdinal = reader.GetOrdinal("ColumnName");
            string columnName = reader.GetString(columnNameOrdinal);
            columnNames.Add(columnName);
        }
        return columnNames;
    }

    public IList<string> ListTables()
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @"SELECT s.name + '.' + t.name AS TableName
            FROM sys.tables t 
            INNER JOIN sys.schemas s ON t.schema_id = s.schema_id";
        commnd.CommandText = sql;
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var tableNames = new List<string>();
        while (reader.Read())
        {
            int tableNameOrdinal = reader.GetOrdinal("TableName");
            string tableName = reader.GetString(tableNameOrdinal);
            tableNames.Add(tableName);
        }
        return tableNames;
    }

    public IList<string> ListViews()
    {
        using OrmCookbookContext context = CreateDbContext();
        using DbCommand commnd = context.Database.GetDbConnection().CreateCommand();
        const string sql =
            @"SELECT s.name + '.' + v.name AS ViewName 
            FROM sys.views v 
            INNER JOIN sys.schemas s ON v.schema_id = s.schema_id";
        commnd.CommandText = sql;
        commnd.Connection!.Open();
        DbDataReader reader = commnd.ExecuteReader(CommandBehavior.CloseConnection);
        var tableNames = new List<string>();
        while (reader.Read())
        {
            int tableNameOrdinal = reader.GetOrdinal("ViewName");
            string tableName = reader.GetString(tableNameOrdinal);
            tableNames.Add(tableName);
        }
        return tableNames;
    }
}

LINQ to DB

public class DiscoverTablesAndColumnsScenario : IDiscoverTablesAndColumnsScenario
{
    public IList<string> ListColumnsInTable(string schemaName, string tableName)
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    IncludedSchemas = new[] { schemaName },
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) => t.Schema == schemaName && t.Name == tableName
                });
            return dbSchema.Tables.Single().Columns.Select(c => c.ColumnName).ToList();
        }
    }

    public IList<string> ListColumnsInView(string schemaName, string viewName)
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    IncludedSchemas = new[] { schemaName },
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) => t.Schema == schemaName && t.Name == viewName
                });
            return dbSchema.Tables.Single().Columns.Select(c => c.ColumnName).ToList();
        }
    }

    public IList<string> ListTables()
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) => !t.IsView
                });
            return dbSchema.Tables.Select(t => t.SchemaName + "." + t.TableName).ToList();
        }
    }

    public IList<string> ListViews()
    {
        using (var db = new OrmCookbook())
        {
            var dbSchema = db.DataProvider.GetSchemaProvider().GetSchema(db,
                new GetSchemaOptions()
                {
                    GetForeignKeys = false,
                    GetProcedures = false,
                    GetTables = true,
                    LoadTable = (t) => t.IsView
                });
            return dbSchema.Tables.Select(t => t.SchemaName + "." + t.TableName).ToList();
        }
    }
}

LLBLGen Pro

TODO

NHibernate

TODO

RepoDb

TODO

ServiceStack

TODO