Populate A List With All The Table Names Which Is Having Specified Columns Through Sql In C#

I have a database in sql server which is having few tables in it. I need to populate a listbox which contains a list of tables names from the database which contains a specified co

Solution 1:

You can use this linq query (now tested):

List<string> tNames= new List<string>();  // fill it with some table names
List<string> columnNames = new List<string>() { "special" };
// ...

IEnumerable<DataRow> tableRows = con.GetSchema("Tables").AsEnumerable()
  .Where(r => tNames.Contains(r.Field<string>("TABLE_NAME"), StringComparer.OrdinalIgnoreCase));
foreach (DataRow tableRow in tableRows)
{
    String database = tableRow.Field<String>("TABLE_CATALOG");
    String schema = tableRow.Field<String>("TABLE_SCHEMA");
    String tableName = tableRow.Field<String>("TABLE_NAME");
    String tableType = tableRow.Field<String>("TABLE_TYPE");
    IEnumerable<DataRow> columns = con.GetSchema("Columns", new[] { database, null, tableName }).AsEnumerable()
        .Where(r => columnNames.Contains(r.Field<string>("COLUMN_NAME"), StringComparer.OrdinalIgnoreCase));
    if (columns.Any())
    {
        tables.Add(tableName);
    }
}

Solution 2:

IMHO you should simply query the INFORMATION_SCHEMA.COLUMNS table instead of trying to filter the returned schema. First retrieving the hole schema to just throw most of the data away is totally ineffective.

SELECT c.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS c
WHERE c.COLUMN_NAME = 'YourLovelyColumnName'

Solution 3:

Assuming you are working on SQL Server:

IF COL_LENGTH('table_name','column_name') IS NOT NULL
 BEGIN
 /*Column exists */
 END

See more:

How to check if column exists in SQL Server table