Get SQL Datatable Names using VB.Net

Question

Hello,

I am trying use VB.Net 2010 express to get all the Table names from my data base. Below is my code:

Dim

conStr As String = ("Server=(local)\sqlexpress; DataBase= " & ParmDataBase & "; Integrated Security=SSPI")

Dim DBCon As New SqlConnection(conStr)

strSQL =

"SELECT * FROM INFORMATION_SCHEMA.tables"

Dim da As SqlDataAdapter = New SqlDataAdapter(strSQL, DBCon)

Dim ds As New DataSet()

da.Fill(ds)

Dim MyTableCount As Integer = ds.Tables.Count

For Each table As DataTable In ds.Tables

ComboBox1.Items.Add(table.TableName)

Next

The problem is that that my dataset (ds) is not filled with my database information. For example, I have 4 tables but the ds.Tables.Count returns 1 and the only table name I get is "Table". 

wharding

Answer 1

Here ADO.NET will create one table with 4 rows containing all table names. So instead of iterating with table collections you should iterate with rows collection of table. i.e.

For Each row As DataRow In ds.Tables(0).Rows
ComboBox1.Items.Add(row.Item("TABLE_NAME"))
Next row

---

Lingaraj Mishra

Answer 2

Thank you Linqaraj. This is exactly what I needed. This also helps my understanding working with datasets (which I am just learning).

Regards,

Wharding

Answer 3

You can alos get list of database objects using SQL SMO API. Following link contains information about how to work with SQL SMO

http://www.mssqltips.com/sqlservertip/1826/getting-started-with-sql-server-management-objects-smo/

---

Val Mazur Microsoft MVP

http://www.xporttools.net