none
GetSchema("Tables") returns empty list. RRS feed

  • Question

  • Hello.

     

    Here is the block of code:

    System.Data.DataTable dataTable = sqlConnection.GetSchema("Databases");
                
                foreach (System.Data.DataRow row in dataTable.Rows)
                {
                  var restrictions = new [] {(string)row[0]};
                  var tables = sqlConnection.GetSchema("Tables", restrictions);
    
                  var tableNames = tables.Rows.Cast<DataRow>().Aggregate(new StringBuilder(),
                                              (sb, r) => sb.Append(r[0])
                                                      .Append(" - ")
                                                      .AppendLine((string)r[2])).ToString();
                }
    

    But tableNames almost always equals empty string. I receive exact list of tables only for one DB. But for many of them I'm dbowner with read/write permissions.

    So It's not clear to me what I'm doing wrong.

    Wednesday, August 31, 2011 5:26 AM

Answers

  • Hi Voronin_Pavel,

    As far as I know, if initial catalog is not specified explicitly default db is assigned. The default db is master. And GetSchema("Tables")  called for every master db on the server, not every DB. Please try the following code:

    static void Main()
            {
                string connectionString = GetConnectionString();
                using (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);" +  //if you want to assign some db, you can add it here.
                   "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("============================");
                }
            }

    And you also can refer to this: http://msdn.microsoft.com/en-us/library/ms254934(v=VS.100).aspx

    I hope this can help you.

     

    have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Wednesday, September 7, 2011 8:11 AM
    Moderator
  • I made it this way:

    Cursor.Current = Cursors.WaitCursor;
                            sqlConnection.Open();
                            System.Data.DataTable dataTable = sqlConnection.GetSchema("Databases");
    
                            foreach (System.Data.DataRow row in dataTable.Rows)
                            {
                                using (var tablesConnection = new System.Data.SqlClient.SqlConnection())
                                {
                                    var dbName = row[0] as string;
                                    try
                                    {
                                        sqlConnection.ChangeDatabase(dbName);
                                    }
                                    catch (Exception)
                                    {
                                        continue;
                                    }
    
                                    var tables = sqlConnection.GetSchema("Tables");
                            }
    


     

    Wednesday, September 7, 2011 9:28 AM

All replies

  • I think you need to specify "Tables" for the CollectionName. Below is an example which get the table names from the Northwind database:

        Dim ConnectionString As String = "Data Source=.\SQLEXPRESS;Integrated Security=SSPI;Initial Catalog=Northwind;"
        Dim Connection As New System.Data.SqlClient.SqlConnection(ConnectionString)
        Connection.Open()
        Dim Restrictions() As String = {"Northwind", Nothing, Nothing, Nothing}
        Dim CollectionName As String = "Tables"
        Dim dt As DataTable = Connection.GetSchema(CollectionName, Restrictions)
        DataGridView1.DataSource = dt
        Connection.Close()
    
    
    




    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, September 1, 2011 4:23 PM
  • Well, real problem is that even initial catalog is not specified explicitly default db is assigned.

    So GetSchema("Tables") shoul be called for every db on the server.


    Thursday, September 1, 2011 6:20 PM
  • It doesn't appear to work that way. It's either by design, a bug or there is another way to do this that isn't particularly obvious.

    If you omit the database or "initial catalog" from the connection string then it returns tables of the master, which would appear to indicate that you're restricted to the database you connect to when retrieving schema objects.


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Thursday, September 1, 2011 7:39 PM
  • It may be other db, not necessarily 'master'.

    I thought GetSchema("Tables") returns all tables for all databases.

    Friday, September 2, 2011 8:46 AM
  • Perhaps someone from Microsoft can confirm whether there is a way to do this. Based upon my testing it only looks at one database (if specified).


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Friday, September 2, 2011 1:19 PM
  • Hi Voronin_Pavel,

    As far as I know, if initial catalog is not specified explicitly default db is assigned. The default db is master. And GetSchema("Tables")  called for every master db on the server, not every DB. Please try the following code:

    static void Main()
            {
                string connectionString = GetConnectionString();
                using (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);" +  //if you want to assign some db, you can add it here.
                   "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("============================");
                }
            }

    And you also can refer to this: http://msdn.microsoft.com/en-us/library/ms254934(v=VS.100).aspx

    I hope this can help you.

     

    have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.


    Wednesday, September 7, 2011 8:11 AM
    Moderator
  • I made it this way:

    Cursor.Current = Cursors.WaitCursor;
                            sqlConnection.Open();
                            System.Data.DataTable dataTable = sqlConnection.GetSchema("Databases");
    
                            foreach (System.Data.DataRow row in dataTable.Rows)
                            {
                                using (var tablesConnection = new System.Data.SqlClient.SqlConnection())
                                {
                                    var dbName = row[0] as string;
                                    try
                                    {
                                        sqlConnection.ChangeDatabase(dbName);
                                    }
                                    catch (Exception)
                                    {
                                        continue;
                                    }
    
                                    var tables = sqlConnection.GetSchema("Tables");
                            }
    


     

    Wednesday, September 7, 2011 9:28 AM
  • And then? It works or it returns empty list?


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 8, 2011 3:27 AM
    Moderator
  • No, it works fine  now cause I switch context each time.
    Thursday, September 8, 2011 3:52 AM
  • OK, happy to see that. Thanks for sharing your solution.

     

    Have a nice day,


    Jackie Sun [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, September 8, 2011 4:38 AM
    Moderator