Is there an way using ADO.NET to determine if a table exists in a database that works with any data provider?

Question

I'm currently doing something like this:

bool DoesTableExist(string tableName)
{
    DbCommand command = this.dbConnection.CreateCommand();
    command.CommandText = "SELECT 1 FROM " + tableName;
    try
    {
        using (DbDataReader reader = command.ExecuteReader())
        {
            return true;
        }
    }
    catch (DbException)
    {
        return false;
    }
}

I'm hoping that there is a way that doesn't involve catching exceptions.

You are probably asking this question because you detected some code smell there, so take this as a compliment: using exceptions as flow control is not best practice. — ErikE, Aug 20 '10 at 07:28

score 25 · Accepted Answer · edited Feb 02 '17 at 20:24

Well, you can use the Connection.GetSchema("TABLES") method.

This returns a DataTable which will contains rows of all the tables in your DB. From here you can check against this and see if the table exists.

This can then be taken a step further:

    private static bool DoesTableExist(string TableName)
    {
        using (SqlConnection conn = 
                     new SqlConnection("Data Source=DBServer;Initial Catalog=InitialDB;User Id=uname;Password=pword;"))
        {
            conn.Open();

            DataTable dTable = conn.GetSchema("TABLES", 
                           new string[] { null, null, "MyTableName" });

            return dTable.Rows.Count > 0;
        }
    }

If you're using .NET 3.5, then you can make this an extension method as well.

Does not work when a transaction is open (see: https://social.msdn.microsoft.com/Forums/en-US/b4a458d0-65bd-40fb-bc60-c7ed8e94517f/sqlconnectiongetschema-exceptions-when-in-a-transaction?forum=adodotnetdataproviders). — Dejan, Dec 16 '14 at 14:40

score 2 · Answer 2 · answered Jan 26 '17 at 14:28

Small improvement on Kyle's answer to account for the fact that different databases (oracle vs ms-sql-server for instance) place the table-name column in a different index in the "Tables" table:

    public static bool CheckIfTableExists(this DbConnection connection, string tableName) //connection = ((DbContext) _context).Database.Connection;
    {
        if (connection == null)
            throw new ArgumentException(nameof(connection));

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

        var tableInfoOnTables = connection //0
            .GetSchema("Tables")
            .Columns
            .Cast<DataColumn>()
            .Select(x => x.ColumnName?.ToLowerInvariant() ?? "")
            .ToList();

        var tableNameColumnIndex = tableInfoOnTables.FindIndex(x => x.Contains("table".ToLowerInvariant()) && x.Contains("name".ToLowerInvariant())); //order
        tableNameColumnIndex = tableNameColumnIndex == -1 ? tableInfoOnTables.FindIndex(x => x.Contains("table".ToLowerInvariant())) : tableNameColumnIndex; //order
        tableNameColumnIndex = tableNameColumnIndex == -1 ? tableInfoOnTables.FindIndex(x => x.Contains("name".ToLowerInvariant())) : tableNameColumnIndex; //order

        if (tableNameColumnIndex == -1)
            throw new ApplicationException("Failed to spot which column holds the names of the tables in the dictionary-table of the DB");

        var constraints = new string[tableNameColumnIndex + 1];
        constraints[tableNameColumnIndex] = tableName;

        return connection.GetSchema("Tables", constraints)?.Rows.Count > 0;
    }
    //0 different databases have different number of columns and different names assigned to them
    //
    //     SCHEMA,TABLENAME,TYPE -> oracle
    //     table_catalog,table_schema,table_name,table_type -> mssqlserver
    //
    //  we thus need to figure out which column represents the tablename and target that one

Is there an way using ADO.NET to determine if a table exists in a database that works with any data provider?

2 Answers2

Linked