<![CDATA[UNKNOWN
'**************************************
' Name: Completely Reusable SQL Server/X
' ML Generator !
' Description:This is a really simple wa
' y of grabbing the column details for all
' the user tables in a SQL Server database
' , and converting those details to XML. I
' f you wanted to get a little creative, y
' ou could re-use this with XSL to create
' SQL and ASP templates for your apps.
' By: Brandon McPherson
'
'
' Inputs:The only real 'condition' is th
' at you should have a default database se
' t up in your connection.
'
' Returns:XML data outlining the column
' information for the tables in a database
' .
'
'Assumes:I know this works in SQL Server
' 2000 and version 7.0, but I won't guaran
' tee anything before that (I don't have c
' opies of the system tables to check).
'
'Side Effects:None
'This code is copyrighted and has limite
' d warranties.
'Please see http://www.Planet-Source-Cod
' e.com/xq/ASP/txtCodeId.6504/lngWId.4/qx/
' vb/scripts/ShowCode.htm
'for details.
'**************************************
<%
Set Cnn1 = Server.CreateObject("ADODB.Connection")
Set adoCmd = Server.CreateObject("ADODB.Command")
Dim sXML
' Set the dsn up to a database
Cnn1.Open "DSN=nwind;UID=sa;PWD="
Set adoCmd.ActiveConnection = Cnn1
' I'm sure there's a much more graceful
' way to do this....
adoCmd.CommandText = "select TableName=t1.[name], ColumnName=c1.[name], datatype=(select a1.[name] from dbo.systypes as a1 where a1.xusertype = c1.xusertype), c1.isnullable, c1.length, c1.colid from dbo.syscolumns as c1 inner join dbo.sysobjects as t1 on c1.id = t1.id where t1.xtype = 'U'"
Set tmpRST = adoCmd.Execute
sXML = "<?xml version=""1.0""?><tables>"
Dim sTable
Dim iColCount
Dim intIterator
iColCount = tmpRST.Fields.Count - 1
sTable = tmpRST.Fields("TableName")
sXML = sXML & "<table><name>" & tmpRST("TableName") & "</name>"
Do While Not tmpRST.EOF
If tmpRST.Fields("TableName") <> sTable Then
sXML = sXML & "</table><table><name>" & tmpRST.Fields("TableName") & "</name>"
End If
sXML = sXML & "<column>"
For intIterator = 1 To iColCount
sXML = sXML & "<" & tmpRST.Fields(intIterator).Name & ">" & tmpRST.Fields(intIterator) & "<!--" & tmpRST.Fields(intIterator).Name & "-->"
Next
sXML = sXML & "</column>"
sTable = tmpRST.Fields("TableName")
tmpRST.MoveNext
Loop
sXML = sXML & "</table></tables>"
Response.Write sXML
%>
]]>