Looping Through All Tables in a SQL Server Database

  • Thread starter Thread starter OutdoorGuy
  • Start date Start date
O

OutdoorGuy

Greetings,

I am attempting to use VB.NET 2003 to loop through all of the tables in
a SQL Server 2005 database. However, I have yet to figure this out.
Does anyone have any suggestions? My existing code is below. Thanks in
advance!

Private Sub btnInsertDB_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsertDB.Click

Dim i As Integer
Dim i As Integer
Dim j As Integer
Dim sqlComm As New SqlClient.SqlCommand
Dim sqlConn As New SqlClient.SqlConnection

sqlConn.ConnectionString =
"SERVER=EMPIRE100;DATABASE=Mytest;trusted_connection=true;connection
timeout=30"
sqlConn.Open()
sqlComm.Connection = sqlConn

'// Code fails here
For j = 0 To sqlComm.Connection.Database
MessageBox.Show(j.ToString)
Next
...
 
Use this SQL Query to return all of the tables in a database:

SELECT * FROM ACH.sys.tables

Then just loop through the resultset.
 
OutdoorGuy said:
Greetings,

I am attempting to use VB.NET 2003 to loop through all of the tables in
a SQL Server 2005 database. However, I have yet to figure this out.
Does anyone have any suggestions? My existing code is below. Thanks in
advance!

Private Sub btnInsertDB_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btnInsertDB.Click

Dim i As Integer
Dim i As Integer
Dim j As Integer
Dim sqlComm As New SqlClient.SqlCommand
Dim sqlConn As New SqlClient.SqlConnection

sqlConn.ConnectionString =
"SERVER=EMPIRE100;DATABASE=Mytest;trusted_connection=true;connection
timeout=30"
sqlConn.Open()
sqlComm.Connection = sqlConn

'// Code fails here
For j = 0 To sqlComm.Connection.Database
MessageBox.Show(j.ToString)
Next
...
Click to expand...

I use the query:

select * from information_schema.tables

to obtain a list of tables in a database.
 
Thanks. However, when I execute the following code I receive the error,
"Run-time exception thrown : System.Data.SqlClient.SqlException -
Invalid object name 'ACH.sys.tables'." Is there something I'm missing
here?

Thanks!

sqlComm.CommandText = "Select * from ach.sys.tables"
strResult = sqlComm.ExecuteNonQuery()
 
Back
Top