CODEWITHSUNDEEP
sql-server-2008odbclinked-server
Brandon Moore
Brandon Moore

Reputation: 8780

Retrieving list of tables from a linked server using odbc drivers

I have a linked server in SQL that uses ODBC drivers. If I already know the name of the table I want see I can retrieve it like so:

select * from GPData..root.TableName

The problem is that the only way I know to view all the tables available is to open up Microsoft Access and act like I'm going to create a linked table and during the process it will list the tables for me to choose from.

Is there a way I could list the tables directly from SSMS? And if not, how might I go about doing it programmactically (preferrably in C#)? There must be some way since Access is obviously able to do it.

Upvotes: 0

Views: 6754

Answers (1)

Brandon Moore
Brandon Moore

Reputation: 8780

Here's how I did it in C#. I created a form with two text boxes. DSNTextBox takes the name of the ODBC DSN and OutputFileText takes the directory you want to place the generated sql file in, which contains commands to create views for each table it finds.

After running the sql file, I am able to access the tables through views as if they were part of my own database. And this way I can browse the table names when I am writing queries instead of having to know in advance what the tables are called.

    OdbcConnection con = new OdbcConnection();
    con.ConnectionString = "DSN=" + DSNTextBox.Text;
    con.Open();

    DataTable schema = con.GetSchema("tables");

    foreach (DataRow r in schema.Rows)
    {
        var name = r["TABLE_NAME"];
        sqlLines.Add("create view " + name + " as select * from " + DSNTextBox.Text + "..root." + name);
        sqlLines.Add("go");
    }
    con.Close();

    if(OutputFileTextBox.Text.Substring(OutputFileTextBox.Text.Length - 1, 1) != "\\") 
        OutputFileTextBox.Text += "\\";

    File.WriteAllLines(OutputFileTextBox.Text + DSNTextBox.Text + ".sql" , sqlLines.ToArray());

Upvotes: 1

Related Questions