Cannot retrieve ALL datatable names from database

Question

I am developing some code to tabulate the datatable names and their corresponding columns from a database. I want to have the list that I can see on the MS SQL Report Builder - Query Designer:

Here's my code:

SqlConnection conn = new SqlConnection("Data Source = server; Initial catalog = Catalog; Integrated Security = true");
conn.Open();
DataTable dt = conn.GetSchema("Tables");
List<string> tablenames = new List<string>();
foreach(DataRow dr in dt.Rows){
     string table = (string)dr[2];
     if((string)dr[1] == "dbo"){
          //Creating a list of table names for "dbo" schema
          tablenames.Add(table);
     }
}
foreach(string table in tablenames){
     if(true){
          SqlDataReader reader = new SqlCommand("SELECT * FROM " + table, conn).ExecuteReader();
          //Iterating the entire list of table names and getting the column names
          for(int column = 0; column < reader.FieldCount; column++){
               Console.WriteLine("Catalog - dbo - " + table + " - " + reader.GetName(column));
          }
          reader.Close();
     }
}
Console.WriteLine("END");
Console.ReadLine();

However, the list that I get doesn't display (among others) the first table "AccountBillingCode", even though I know it's contained within the list of strings. If I change the statement:

if(true)

by

if(table.StartsWith('A'))

Then, "AccountBillingCode" is listed in the output.

I don't understand why there are some tables getting excluded from my code.

Any ideas?

Thanks!

`if (true)` is always true, what's its function there? BTW, your select statement should include the schema `SELECT * FROM dbo.tableName` — McNets, May 11 '22 at 08:44
None. I added it there just to verify that if change that by `if(table.StartsWith('A'))` then I actually see the content from "AccountBillingCode" — Pablo D., May 11 '22 at 08:47
Using INFORMATION_SCHEMA.TABLES you get the list of table i.e `SELECT * FROM INFORMATION_SCHEMA.TABLES` or you can query over `sys.schemas` . Is this what you looking for https://stackoverflow.com/questions/420741/getting-list-of-tables-and-fields-in-each-in-a-database — Satinder singh, May 11 '22 at 08:47
I have sorted the list before getting the column names and I obtain more results. But I still don't understand what could be failing. — Pablo D., May 11 '22 at 08:48
Use `sys.tables` and `sys.columns`; don't write a wretched loop like this. — Jeroen Mostert, May 11 '22 at 08:50
Sidep point: connection, command and reader objects need `using` to dispose them — Charlieface, May 11 '22 at 11:22

score 1 · Answer 1 · answered May 11 '22 at 08:52

1

Something like this as an idea.

select 
t.name,c.name
from
sys.tables as t
left join sys.columns as c on t.object_id=c.object_id
order by t.name,c.column_id

answered May 11 '22 at 08:52

Nathan_Sav

8,466
2
13
20

score 0 · Answer 2 · answered May 11 '22 at 08:56

0

Apparently, there's nothing wrong with the code. It's just the code is too long for the Console, and when copying from there, the content at the top is missing. Disappointing mystery this time. Sorry! Thanks for the answers anyway!

answered May 11 '22 at 08:56

Pablo D.

172
1
2
11

Cannot retrieve ALL datatable names from database

2 Answers2