How to retrieve table names from SQL Server in ASP

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

How to retrieve table names from SQL Server in ASP

Author

Topic

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-07 : 01:27:14

Hi

I want to retrieve table names from SQL Server database through ASP page.I tried with the following code,but it gives error like:server or provider is unable to process this

code is:

<%
' Create a connection object
Set Conn = Server.CreateObject ("ADODB.Connection")

' Open the connection to the database. I use a system DSN here, but
' you can use whatever method you wish
'Conn.Open "DSN=Library"

DSNtest="DRIVER={SQL Server};SERVER=SERVER;UID=sa;PWD=thanks;DATABASE=parameter"
Conn.open DSNtest

' Open the database schema to query the list of tables. Extract the
' list in a Recordset object
Set Rs = Conn.OpenSchema(adschematables)

' Loop through the list and print the table names
Do While Not Rs.EOF
Response.Write "<BR>" & Rs ("TABLE_NAME")
Rs.MoveNext
Loop

' Close and destroy the recordset and connection objects
Rs.Close
Set Rs = Nothing

Conn.Close
Set Conn = Nothing
%>

Please give me the solution

Mamatha

Kristen
Test

22859 Posts

Posted - 2005-01-07 : 01:55:11

SELECT TABLE_NAME FROM information_schema.tables

any use?

Kristen

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-07 : 02:43:05

You can also do it via OpenSchema calls (which should be a bit database independent).

http://www.mindsdoor.net/VB/ADOScema.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-07 : 04:49:11

Hi kristen

Thank you vaery much,its working fine,but it shows all the tables with some system tables also.How can i find the are system tables and these are user tables.Is there any identification for that query at run time?.

Mamatha

nr
SQLTeam MVY

12543 Posts

Posted - 2005-01-07 : 05:03:01

You will also need
where table_type = 'base table'

Otherwise you will get views.
That should just leave you with dtproperties as an extra.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources