[thelist] Database Schema from ASP

[Scott Webster]

Mike

: ------------------------------------------
From: "Mike Stenhouse" <Mike.Stenhouse at spiritsoft.com>
: Does anyone know if it's possible to get the Primary Keys Schema of a
: SQL Server database with ASP?
:
: I've used myConnection.OpenSchema(someSchema) to get table and column
: schema data but neither have the PK info... I tried OpenSchema(28),
: which ought to get the Primary Keys schema, and all the right fields are
: there (TABLE_NAME, PK_NAME etc) but it's a recordset of 0 records. I do
: definitely have my primary keys defined! Ideally I'd like to retrieve
: the Foreign Key schema (27) as well but that doesn't seem to work either
: despite having my relationships nicely diagrammed.
:
: Does ADO not support these schemas and if not, is there any other way to
: get them?
:
: Here's my code:
: ------------------------------------------
: var oConn = Server.CreateObject("ADODB.Connection");
: var oTable = Server.CreateObject("ADOX.Catalog");
:
: oConn.Open(connData);
: oTable = oConn.OpenSchema(28);
:
: var s = "";
: s += oTable.RecordCount+"<br />";
: var eCols = new Enumerator(oTable.Fields);
: while (!eCols.atEnd()) {
:     s += eCols.item().Name + "<br />";
:     eCols.moveNext();
: }
: Response.write(s);
: ------------------------------------------

I don't use JScript but your code looks fine. The VBScript
below is essentially the same and I'm getting results...

set conn = Server.CreateObject ("ADODB.Connection")
conn.Open "urConnectionString"

'set rs = conn.OpenSchema(adSchemaTables) ' 20
'set rs = conn.OpenSchema(adSchemaForeignKeys) ' 28

do while not rs.eof

  for each field in rs.fields
    response.write(field.name & " = " & field.value & "<br />"
  next
  response.write("<hr />")

  rs.MoveNext
loop

'cleanup
---------------

Basically a "it works for me" response ;)

FWIW, I'm running w2k sql2k. Fully SPd.

hth