Return all Tables?

morpheus · December 15th, 2003, 03:16 PM

How can I show all the tables in a database? I want to see every table but I don't have access to the server tools. I use asp to work with the database I would like to see every table that's there.

Thanks

Jeff Mason · December 15th, 2003, 03:24 PM

Look at the INFORMATION_SCHEMA.TABLES system view, e.g.

Code:

SELECT * FROM INFORMATION_SCHEMA;

will display a list of all the tables in the current database. The column TABLE_TYPE will indicate whether the table is a BASE_TABLE or a VIEW.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

morpheus · December 15th, 2003, 03:44 PM

SQLStr="SELECT * FROM INFORMATION_SCHEMA"

I use this but I get an error "Invalid object name"

Thanks for the response

Jeff Mason · December 15th, 2003, 03:52 PM

Sorry. The view name is INFORMATION_SCHEMA.TABLES. The code should read:

Code:

SELECT * FROM INFORMATION_SCHEMA.TABLES

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

morpheus · December 15th, 2003, 03:57 PM

Thanks, it works great

method · March 3rd, 2005, 01:38 PM

can any one help how to write asp script that display a table from sql server using select * from information_schema.tables

rodmcleay · March 3rd, 2005, 08:47 PM

Method,
I see the connection but when you ask a new question it is best to start a new topic.

To display table data from select query recordset you can:

Code:

sql_string = "select * from TABLENAME"
'===========================================================================================================================
'AREA BELOW DOES NOT NEED TO BE CHANGED, JUST CHANGE THE SQL STRING ABOVE                                                   
'===========================================================================================================================
Response.write("=============================================<BR>")
Response.write("This page displays the results for the query:<BR>")
Response.write(sql_string & "<BR>")
Response.write("=============================================<BR>")
set recordset = objDBConn.execute(sql_string)

'Has been modified to print a table with the 
'table fields as headers and then the records to follow
'Just need to change the sql_string above
If Not recordset.EOF And Not recordset.BOF Then
    count = 0 
    wt = 0
    cub = 0
    response.write "<table border=1><tr>"
    For i = 0 To recordset.Fields.Count - 1
       response.write "<td><b>" & recordset(i).Name  & "</b></td>"
    Next
    response.write "</tr>"
    while not recordset.eof
        response.write "<tr>"            
        For i = 0 To recordset.Fields.Count - 1
            if (not isnull(recordset(i).Value)) then 
                response.write "<td>" & recordset(i).Value  & "</td>"
            else
                response.write "<td>&nbsp;</td>"
            end if
        Next            
        response.write "</tr>"
        count = count + 1
        recordset.movenext
    wend
    response.write "</table>"
else
    response.write "No Matches"    
End If
response.write "<br>Total Records: " & count & "<br><br>"
response.write "...Completed"


recordset.close

objDBConn.close 
'================================================================================================================================

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

method · March 7th, 2005, 08:32 AM

Quote:

quote:Originally posted by rodmcleay
Method,
I see the connection but when you ask a new question it is best to start a new topic.

To display table data from select query recordset you can:

Code:

sql_string = "select * from TABLENAME"
'===========================================================================================================================
'AREA BELOW DOES NOT NEED TO BE CHANGED, JUST CHANGE THE SQL STRING ABOVE                                                   
'===========================================================================================================================
Response.write("=============================================<BR>")
Response.write("This page displays the results for the query:<BR>")
Response.write(sql_string & "<BR>")
Response.write("=============================================<BR>")
set recordset = objDBConn.execute(sql_string)

'Has been modified to print a table with the 
'table fields as headers and then the records to follow
'Just need to change the sql_string above
If Not recordset.EOF And Not recordset.BOF Then
    count = 0 
    wt = 0
    cub = 0
    response.write "<table border=1><tr>"
    For i = 0 To recordset.Fields.Count - 1
       response.write "<td><b>" & recordset(i).Name  & "</b></td>"
    Next
    response.write "</tr>"
    while not recordset.eof
        response.write "<tr>"            
        For i = 0 To recordset.Fields.Count - 1
            if (not isnull(recordset(i).Value)) then 
                response.write "<td>" & recordset(i).Value  & "</td>"
            else
                response.write "<td>&nbsp;</td>"
            end if
        Next            
        response.write "</tr>"
        count = count + 1
        recordset.movenext
    wend
    response.write "</table>"
else
    response.write "No Matches"    
End If
response.write "<br>Total Records: " & count & "<br><br>"
response.write "...Completed"


recordset.close

objDBConn.close 
'================================================================================================================================

======================================
They say, best men are molded out of faults,
And, for the most, become much more the better
For being a little bad.
======================================

Thank u for reply . But what i meant is using
SELECT * FROM INFORMATION_SCHEMA.TABLES.I do not see any usage of meta data here!i mean information about tables , columns,datatype , primary keys and foreign keys should not be hardcoded. for example i want to write and asp script that uses meta data of northwind database and display all records of Customers tables.Thanks and looking forward for u reply. Since i realy need to learn this meta data usage well .Thanks