Looping Through All Tables in a SQL Server Database

12 Dec 2006 7:35 PM

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
   ...

*** Sent via Developersdex http://www.developersdex.com ***

12 Dec 2006 8:40 PM

RSH

Use this SQL Query to return all of the tables in a database:

SELECT * FROM ACH.sys.tables

Then just loop through the resultset.

Show quoteHide quote

"OutdoorGuy" <Outdoor***@fishing.com> wrote in message
news:uBYXnSiHHHA.4804@TK2MSFTNGP03.phx.gbl...
> 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
>   ...
>
>
>
> *** Sent via Developersdex http://www.developersdex.com ***

13 Dec 2006 12:09 AM

OutdoorGuy

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()

*** Sent via Developersdex http://www.developersdex.com ***

12 Dec 2006 9:32 PM

zacks

OutdoorGuy wrote:
Show quoteHide quote

> 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
>    ...

I use the query:

select * from information_schema.tables

to obtain a list of tables in a database.