Get the table on the database

Hi Experts, I wish to obtain all Tables that is inside my database. I want to get their names and put it in the table. I have code below but I have no idea how do it. I have DataBase named, "MyDataBase"

<%

response.expires=-1

Set cn = Server.CreateObject("ADODB.Connection")
Set rs = Server.CreateObject("ADODB.Recordset")

cn.ConnectionString = "driver={SQL Server};server=MyNoteBook\SQLEXPRESS;uid=sa;pwd=sa;database=" & "MyDataBase" & ""
cn.Open

%>

Open in new window

Whing Dela CruzAsked:
Who is Participating?
 
MishaProgrammerCommented:
No problem, you can use "order by".
SELECT TABLE_NAME FROM MyDataBase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME 

Open in new window

0
 
MishaProgrammerCommented:
tre this code:
SELECT TABLE_NAME FROM MyDataBase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'

Open in new window

1
 
Whing Dela CruzAuthor Commented:
Hi Misha, How could I correct this code? This code is base on your suggested above.

   Set rs = cn.Execute("SELECT TABLE_NAME FROM MyDataBase.INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='" & "BASE TABLE" & "'")
           with rs
               If rs.BOF = True Then 'Not exist
         		    response.write("Database is not exist")
         			 response.end()
               Else
                  do until rs.EOF
                     if msg = "" then
                         msg = trim(rs("BASE TABLE"))
                     else
                        msg = msg & "/" & ( trim(rs("BASE TABLE"))) 
                     end if  
                     rs.MoveNext
                  loop

                  response.write(msg)
                  response.end()
                  cn.close
               end if
           End with

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
MishaProgrammerCommented:
Does this code work? Or not?
0
 
Whing Dela CruzAuthor Commented:
It's working sir, I just changed the "trim(rs("BASE TABLE"))" to trim(rs("TABLE_NAME"))  now perfectly working. However, before I close this question, is there any tricks here to show the table alphabetically, base on the code above?
0
 
MishaProgrammerCommented:
Also you can sorted result in  decrease
SELECT TABLE_NAME FROM Test1.INFORMATION_SCHEMA.TABLES
 WHERE TABLE_TYPE = 'BASE TABLE' order by TABLE_NAME desc

Open in new window

0
 
Whing Dela CruzAuthor Commented:
Thanks a lot sir, every code you have given me are working and help me a lot...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.