Is there any general SQL way to get tables names and create tables?

With "table names" I mean just the name of normal (not queries or stuff like that), plain old tables. This is because I'm working on a project that currently connects to the Jet engine and among other features, it shows a list of tables that the user would double click to see some specific's table contents. But now I want the user to be able to change the engine from a list of installed engines. But for my program to work with other engines it will need to get the table names in a way that will work for every SQL engine (or at least most of them). I actually also need to be able to get all the column names for a specific table, and also, be able to create a "CREATE TABLE" query in a way that will work with every possible engine (since the user can create tables from a wizard, and my program generates the query). I'm actual开发者_开发百科ly very doubtful that this is possible but, as far as I know, Visual Studio can create tables from a wizard for different database engines. How do they manage to do this? Will I have to have a different "CREATE TABLE" query for every possible SQL engine?

I'm wondering if ADO can help with this as it seems to have everything somehow standardized.

No, unfortunately there is no general way to do these things as far as I know. All DB engines have slightly different dialects of DDL and SQL, support different sets of data types, and different ways of managing their metadata etc. If you keep to the absolute lowest denominator of features I guess you could rely on standard SQL/DDL but that will be very limited.

Usually this is solved by creating an abstract data layer with several different implementations which handles the differences.

ADO only solves part of the problem as it offers a common interface for sending queries to a database but the SQL in the queries have to be specified by the client.

If you want any back-end, there will always be one that does not work, but nearly every back-end will allow:

select table_name from information_schema.tables

Your basic create table commands, with keys and indexes, are easily coded to be compatible with nearly every back-end, execpt for auto-incremented integers keys, which have a different syntax on every back end.

So the answer, "mostly yes, probably more than you think, but not 100%". Because the quirks are small, it is possible to write some general code with some tweaks for the particular back-end.

This should do it for you in MSSQL. I imagine it would be very similar for other SQL implementations.

SELECT DISTINCT Name FROM sysobjects WHERE xtype='U'

You can use the GetSchema ADO function to get a DataTable with almost all schema data.

This example use a SQLConnection, but the function can be used in any ODBCConnection.

using System;
using System.Data;
using System.Data.SqlClient;

class Program
{
  static void Main()
  {
  string connectionString = GetConnectionString();
  sing (SqlConnection connection = new SqlConnection(connectionString))
  {
   // Connect to the database then retrieve the schema information.
   connection.Open();
   DataTable table = connection.GetSchema("Tables");

   // Display the contents of the table.
   DisplayData(table);
   Console.WriteLine("Press any key to continue.");
   Console.ReadKey();
   }
 }

  private static string GetConnectionString()
  {
   // To avoid storing the connection string in your code,
   // you can retrieve it from a configuration file.
   return "Data Source=(local);Database=AdventureWorks;" +
      "Integrated Security=true;";
  }

  private static void DisplayData(System.Data.DataTable table)
  {
     foreach (System.Data.DataRow row in table.Rows)
     {
        foreach (System.Data.DataColumn col in table.Columns)
        {
           Console.WriteLine("{0} = {1}", col.ColumnName, row[col]);
        }
     Console.WriteLine("============================");
     }
  }
}

In Visual Studio it is implemented using the Data Designer Extensibility (DDEX) where a specific provider should expose GetSchema method to help retrieving metadata. You can get some ideas here.