get list of tables' names in SQL server database

[qfchen]

Hi,

I have one application create tables in database dynamically during run. Another application need to find all these tables' name in the database. how should in do in vb.net?

[vidhyaG]

Hi....

Try this..

1) Using Create table Query You can create a table in run time

2) you can find a table by using this Query

select * from INFORMATION_SCH EMA.tables (OR)
select * from INFORMATION_SCH EMA.tables where table_name=[your table Name]

[qfchen]

but the table name is link to some serial number, such as E80001, the other application only knows the name start with E8, is there any way to get the list of table name, then filter out those start with E8?

[qfchen]

select * from INFORMATION_SCH EMA.tables
above statement, How can I get the list of tables name? where the table names are returned to?

[vidhyaG]

did u execute that query?

select * from INFORMATION_SCH EMA.tables

Above Query Returns list of table name and details of that from particular database.

so you can find list tables from that.

then if you want to find the table with starting text E8...

you can use

select * from INFORMATION_SCH EMA.tables where table_name like '%E8%'

[qfchen]

Thank you very much.
Yes, I did try the statement in SQL server management studio, It works.
But how can I find the result in vb.net after execute the SQL statement? below is my code in VB

Dim Cnt As Integer
        Dim tmpStr As String
        Dim sqlCmd As New SqlClient.SqlCommand("select * from INFORMATION_SCHEMA.tables where table_name like '%E8%'", Me.myConnection)
        sqlCmd.CommandTimeout = 10

        Cursor.Current = Cursors.WaitCursor
        Try
            Me.myConnection.Open()
            Cnt = sqlCmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox("error in frmESDMonitor, backup database:" & ex.Message.ToString)
        End Try
        Me.myConnection.Close()

[vidhyaG]

sorry i dnt know vb very well.so i have given the code in c#.

SqlDataAdapter adp;
SqlConnection con;

adp=new SqlDataAdapter ("select * from INFORMATION_SCH EMA.tables where table_name like '%E8%'",con);

DataTable Dt = new DataTable();
adp.Fill(Ds);

Now Executed table is in Datatable named as Dt.

From that you can do anything what you want.

[qfchen]

thank you very much, the codes works in VB as well. have a good day.