Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Community Forums
 Code Support: ASP (Non-Forum Related)
 SQL Question
 New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

Kurt
Starting Member

30 Posts

Posted - 07 April 2001 :  23:53:46  Show Profile  Visit Kurt's Homepage
I'm trying to create a page that will show me the basica structure of a SQL Server database. I'd like to see the user tables, and all fields within those tables. How hard is that to do?

PJamieson
Starting Member

United Kingdom
11 Posts

Posted - 08 April 2001 :  14:13:33  Show Profile  Visit PJamieson's Homepage
Pretty simple. Use the ADOX catalogue to get the information about your database. You should be able to get Tables, columns, Stored Procedures, etc etc.

Go to Top of Page

Kurt
Starting Member

30 Posts

Posted - 09 April 2001 :  11:08:28  Show Profile  Visit Kurt's Homepage
Well, this seems to work, assuming you have the proper connection stuff setup as "conn":

All these stored procedures and parameters are in the help files for Query Analyzer.


<%
strSQL = "sp_tables '%','dbo',null,""'TABLE'"""
Set rs = conn.execute(strSQL)
Do while not rs.eof
bList = false
response.write rs("TABLE_NAME")
strSQL = "sp_columns '" & rs("TABLE_NAME") & "'"
Set oRS = conn.execute(strSQL)
If not oRS.eof then
response.write "<ul>"
bList = true
End If
Do while not oRS.eof
bStrong = false
response.write "<li>"
If oRS("TYPE_NAME") = "int identity" then
response.write "<strong>"
bStrong = true
End If
response.write oRS("COLUMN_NAME")
If bStrong = true then
response.write "</strong>"
End If
response.write "</li>"
oRS.movenext
loop
If bList = true then response.write "</ul>"
rs.movenext
loop
Set oRS = nothing
Set rs = nothing
Set conn=nothing
%>


Go to Top of Page

Doug G
Support Moderator

USA
6493 Posts

Posted - 09 April 2001 :  18:13:38  Show Profile
If you want some degree of database independence, look at INFORMATION_SCHEMA views. MS has docs at http://msdn.microsoft.com/library/default.asp?URL=/library/psdk/sql/ts_ia-iz_4pbn.htm

You can use the same code to list A2K structures via ADO, and INFORMATION_SCHEMA is now in the SQL standard, so it should be pretty portable.


======
Doug G
======
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 1 seconds. Powered By: Snitz Forums 2000 Version 3.4.07