How use wild card in table name for GetSchema method?

Question

I want query all the existing tables whose name begin with specific string in database using oledb GetSchema method. In other words I am looking for equivalent of the following sql query:

SELECT * FROM information_schema.tables WHERE table_name  LIKE 'customer%'

Something like:

String[] tableRestrictions = new String[4];
tableRestrictions[2] = "%customer";
DataTable customerTables = conn.GetSchema("Tables", tableRestrictions );

You can use that query, but you need to execute it like you would any other SQL query (i.e. using a `IDbCommand`). Not sure why you want to use `GetSchema()`? — CodingGorilla, Jan 18 '16 at 19:13
or you can just loop through the schema yourself. There shouldn't be too many fields. — LarsTech, Jan 18 '16 at 19:17
@LarsTech: actually there are too many tables and I want to see what tables exist in the db. Actually the table names are dynamic for some reasons and I should find the name of table before I can query it. — Saeid, Jan 18 '16 at 19:38
Then you just make a function where you pass the table name, etc. — LarsTech, Jan 18 '16 at 19:54

score 1 · Accepted Answer · edited May 23 '17 at 12:25

In this case the table name I want to query from is Dynamic. Therefore I needed to get the whole table name first.

It seems there is not efficient way to do that but using an iteration. I came to this conclusion that using Linq provides the neatest solution(Thanks to Tim's answer to a similar case):

// Get the tables using GetSchema:
dtbTables = dbConnection.GetSchema("tables");

// use linq to get the table whose name matches the criteria:
DataRow recSpecificTable = dbConnection.GetSchema("Tables").AsEnumerable()
                .Where(r => r.Field<string>("TABLE_NAME")
                .StartsWith("customer")).FirstOrDefault();

// Now the table name I am looking for:
tableName = Convert.ToString(recSpecificTable["TABLE_NAME"]).Trim();

How use wild card in table name for GetSchema method?

1 Answers1