Printing Column Names? 1

[TonyRosen]

I have a SQL database with multiple tables in it. Is there a way to view all the tables with their column names so that I can print the column names?

 

[CassidyHunt]

You can use openschema method. This will open up the names of all the tables in the database. Then you can open up each table and pull out all the fields. Can be done in the same script. Let me know if you need more code. This is just for the tables piece.

Dim cn 
Dim rs 
Dim i

set cn = Server.Createobject("ADODB.Connection")
set rs = Server.Createobject("ADODB.Recordset")

With cn
    .Provider = "Provider here"
    .ConnectionString = "Connection String here"
    .Open
    
End With

Set rs = cn.OpenSchema(adSchemaTables)

do until rs.eof = true
    Response.Write rs("Table_Name") & " "
    Response.Write rs("Table_Type") & "<br>"
    rs.movenext
loop

Keep in mind that this pulls all information out of the database. So you have to test the Table_Type as being Table otherwise you will get a bunch of system garbage.

Hope this gets you going

Cassidy

 

[CassidyHunt]

I went ahead and completed the code to do this. I also tested and know it works on an Oracle database and Access database. I do not have SQL server available to me. It will find all the tables in a database and then print the fields contained in each. I do not know what your connection information would be so please complete that. I put comma's to seperate the values out. You will have to format from there. Let me know if this works for you.

<%
Dim cn 
Dim rs 
Dim sString 
Dim rs2 
Dim i 
Dim i2 

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

With cn
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "c:\Northwind.mdb"
    .Open
End With

Set rs = cn.OpenSchema(20)
rs.MoveFirst
 
For i = 0 To rs.Fields.Count - 1
    If rs("Table_Type") = "TABLE" Then
        sString = sString & rs("Table_Name") & "<BR>"
        rs2.Open "Select * from " & rs("Table_Name"), cn, 1, 3
        For i2 = 0 To rs2.Fields.Count - 1
            sString = sString & rs2.Fields(i2).Name & ","
        Next 
        rs2.Close
        sString = sString & "<P>"
    End If
    
    rs.MoveNext
Next 

Response.Write  sString

Set rs = Nothing
Set rs2 = Nothing
Set cn = Nothing
%>

Please let me know if there is anything else and how this works.

Cassidy

 

[TonyRosen]

ugh. I can't get it to print anything out. I'll keep trying tomorrow. It's getting late.

 

[CassidyHunt]

Here is what I can see for the SQL server connection information:

With cn
    .Provider = "sqloledb"
    .ConnectionString = "Initial Catalog=Contact; Data Source=SQLServer02; User ID=sa; password=sa"
    .Open
End With

Fill in the information in the connection string and it should come up. Might take along time depending on the number of the tables.

Let me know.. Have good night.

Cassidy