Why can I not get the names of my tables using System.Data.SqlClient?

Question

I did not create and do not own this database. My job is usually just to put data in it, so I don't have to think too much about the DBA side. I am messing with a side project and can not figure out why I can't list all the table names. I have found several methods to gather this information, but none work for me in a c# context. I get 0 rows back every time. They do work when executed as queries in azure data studio. I get 99 rows back (the correct number of tables) no matter which method I use to enumerate the tables. My connection string specifies the database to use within the server.

The query named workingQuery returns the expected information (1 row with 15 fields). So I know my connection string is correct and my code is functional.

I have a test function that I am trying many queries on.

using (SqlConnection conn = new SqlConnection(connectionString))
{
    conn.Open();
    List<string> fields = new List<string>();
    string workingQuery = "SELECT TOP (1) * FROM myRealTable";
    string failingTablesQuery1 = "SELECT name FROM sysobjects WHERE xtype = 'U'";
    string failingTablesQuery2 = "SELECT name AS table_name FROM sys.tables GO";
    string failingTablesQuery3 = "SELECT TABLE_NAME FROM information_schema.tables WHERE TABLE_TYPE = 'BASE TABLE'";

    using (var command = conn.CreateCommand())
    {
        command.CommandText = workingQuery;

        SqlDataReader reader = command.ExecuteReader();
        while (reader.Read())
            for (int i = 0; i < reader.FieldCount; i++)
            {
                fields.Add(reader[i].ToString());
            }
        conn.Close();
        return fields; 
    }
}

In addition to the raw sql queries I have also tried SqlConnection.GetSchema("Tables"). It also returns no rows.

I tried prepending USE myDatabaseName; to the front of the queries to no effect.

I can only assume this is an issue with the way the database is set up. Can anyone help me track down what is causing this issue?

SOLUTION: My connection string had credentials that did not have the necessary permissions for this operation.

You say/imply that they aren't working, but you don't tell us why they aren't working. What does "not working" mean? — Thom A
– Thom A ♦, Commented May 11, 2021 at 16:50
I'll edit my question. Working was meant to say I get rows back. Not working was meant to say I get no rows back when I should. — OsakaRhymes
– OsakaRhymes, Commented May 11, 2021 at 16:51
I would think that the sys namespace would be relevant. Somehow objects that are not user objects are not queryable the way your connection string or runtime is behaving. I would put your sysobjects query in a view or stored proc - you would own the view or sp and then see if you can query it. (all of this a guess.) — Sql Surfer
– Sql Surfer, Commented May 11, 2021 at 16:59
Are you using the correct identity? It's possible you lack VIEW DEFINITION permission on the objects. — David Browne - Microsoft
– David Browne - Microsoft, Commented May 11, 2021 at 17:10
@DavidBrowne-Microsoft This was my issue. I thought I was using the same permissions during my manual testing. However, I was actually using my personal credentials when manually testing instead of the production credentials. Thank you. — OsakaRhymes
– OsakaRhymes, Commented May 11, 2021 at 17:52

David Browne - Microsoft · Accepted Answer · 2021-05-11 17:59:11Z

2

In SQL Server access to metadata is controlled by the VIEW DEFINITION permission. You might have permission to SELECT from a table, but not to view its definition or discover it in the catalog. By default any permission on an object implies VIEW DEFINITION, but it can be DENY'd to a user. See Metadata Visibility Configuration.

So check that you're using the same identity in both places and that that identity has VIEW DEFINITION permissions on the tables.

answered May 11, 2021 at 17:59

David Browne - Microsoft

90.7k7 gold badges52 silver badges87 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why can I not get the names of my tables using System.Data.SqlClient?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related