Exactly like SQL Server table order

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

Exactly like SQL Server table order

Author

Topic

Mamatha
Posting Yak Master

102 Posts

Posted - 2005-01-25 : 02:17:02

Hi
i have a code to retrieve table structures of SQL Server database.
It gives all the table names in particular databse with the column names and datatypes also.
But the output displays not in the exact order of what i am designing in SQL Server database,mainly coulmnnames are not in order.
I want to displayed output exactly like what i was designed in SQL Server table.
How can i displyed like that is there any solution?
Please give me reply.
The code is:

<%
'Set Conn = Server.CreateObject("ADODB.Connection")
Set cn = Server.CreateObject("ADODB.Connection")
DSNtest="DRIVER={SQL Server};SERVER=SERVER;UID=sa;PWD=thanks;DATABASE=parameter"
cn.open DSNtest
Set sysObjects = cn.Execute("SELECT * FROM sysObjects WHERE xtype = 'U' ORDER BY name")

If sysObjects.eof Then
Response.write("No records returned")
End If

do until sysObjects.eof
'if not sysObjects("name")="dtproperties" then

Response.write("<table width='100%' border='1'>")
Response.write("<tr><td colspan='3'>Table Name:" & sysObjects("name") & "</td></tr>")
Response.write("<tr><td>Field Name</td><td>Data Type</td><td>Data Type Length</td></tr>")
Set sysColumns = cn.Execute("SELECT A.name As 'ColumnName', B.name As 'DataType', A.length As 'DataTypeLength' FROM sysColumns As A INNER JOIN sysTypes As B On (A.xtype = B.xtype) WHERE id = " & sysObjects("id") & " ORDER BY A.name")

if sysColumns.eof Then
Response.write("<tr><td>No columns found for " & sysObjects("name") & " table.</td></tr>")
end if

do until sysColumns.eof
Response.write("<tr><td>" & sysColumns("ColumnName") & "</td><td>" & sysColumns("DataType") & "</td><td>" & sysColumns("DataTypeLength") & "</td></tr>")
sysColumns.movenext
loop
Response.write("</table>")
Response.write(" ")
sysObjects.movenext
'end if
loop

cn.Close
Set cn = Nothing
%>

Thanks

Mamatha

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-01-25 : 02:45:18

Look up syscolumnsw in BOL. See if the ColOrder column fits your needs.

HTH

=================================================================

Our elections are free, it's in the results where eventually we pay. -Bill Stern, sports announcer (1907-1971)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources