SQL SERVER结构浏览器

从老外那儿转帖过来的

很酷,只要修改几个参数就可以了,很有帮助

1@ LANGUAGE = JScript 
1   
2var ConnStr= "DSN=admin"; //   
3var UserLogin= "sa"; // input empty Login and Password,   
4var UserPassword= ""; // if your DSN works via WinNT trust connection   
5var CharSet= "gb2312"; // as sample "windows-1251"   
6var PgSize= 10;   
 1<html>
 2<head>
 3<meta content="text/html; charset=utf-8" http-equiv="content-type"/>
 4<title>MS SQL Structure Viewer Version 1.2</title>
 5</head>
 6<body alink="#000000" bgcolor="#2f2f2f" leftmargin="1" link="#000000" topmargin="1" vlink="#000000">
 7<table bgcolor="gray" cellpadding="0" cellspacing="0" width="100%"><td>
 8<table cellpadding="0" cellspacing="1" width="100%">
 9<tr><td align="center">
10<font color="white" size="+1"><b>MS SQL WebTools &gt;&gt; Table Structure Viewer</b></font></td>
11<td align="right">
12<font color="black"><small><b>written by   
13<a href="mailto:[email protected]">
14<font color="black"><small><b>Alexander Tkalich</b></small></font></a></b></small></font>
15</td></tr>
16</table>
17</td>
18</table>
19<p>   

var trColor1= "#7f9faf", trColor2= "#bfcfd7";
var trColor= trColor1;

function isDef( Value){
if( Value== ( Value+ "")) return true;
return false;
}

function QOutSelect( Conn, Name, Value, FirstName, FirstValue, SQL, SSize){
var Rs= Conn.Execute( SQL);
Response.Write( "<select name='"+ Name+ "' size="+ SSize+ ">");
if( FirstName!= "")
Response.Write( "<option value='"+ FirstValue+ "'>"+ FirstName);
for( ; !Rs.EOF; Rs.MoveNext()){
Id= Rs( 0);
Nm= Rs( 1);
if( Value== ""+ Id) S= 'selected'; else S= '';
Response.Write( "<option "="" "+="" s+="" value='"+ Id+ "'>"+ Nm+ "\n");
}
Response.Write( "</option></option></select>");
}

var S, DbName, TbName, Tbl, Row;
if( !isDef( DbName= Request.Form( "DbName")))
DbName= Request.QueryString( "DbName");
if( !isDef( TbId= Request.Form( "TbId")))
TbId= Request.QueryString( "TbId");
Tbl= Request.QueryString( "Tbl");
Row= Request.QueryString( "Row")/ 1;

1  
2<center>   
3  

var Conn= Server.CreateObject("ADODB.Connection");
Conn.Open( ConnStr, UserLogin, UserPassword);

if( !isDef( Tbl)){

1  
2<form action="TbStru.asp" method="post" name="f">
3<input name="CurrentDb" type="hidden" value="```
4=DbName
5```"/>
6<table bgcolor="#7f9faf" border="1" cellpadding="0" cellspacing="0"><td>
7<table border="0" cellpadding="8" cellspacing="0"><tr valign="bottom"><td align="center"><b>Databases</b><br/>   
8  

QOutSelect( Conn, "DbName' onChange='document.f.submit();", DbName, "", "",
"select name, name from master.dbo.sysdatabases Order by name", 10);
Response.Write( '</td>');
if( isDef( DbName)){
Response.Write( '<td align="center"><b>Tables &amp; Views</b><br/>');
QOutSelect( Conn, "TbId' onChange='document.f.submit();", TbId, '', '',
"select id, name from "+ DbName+ ".dbo.sysobjects where type in ('U','V') and category&lt;&gt;2 Order By Name", 10);
Response.Write( '</td>');
}
if( isDef( DbName)) S= 'Show structure';
else S= 'Show list of tables';
Response.Write(
"\n<td><table border="0" height="100%">\n"+
"<tr valign="bottom"><td><input type="submit" value='"+ S+ "'/></td></tr>\n"+
"</table></td></tr></table></td></table></form><p>\n"
);
}

if( !isDef( CurrentDb= Request.Form( "CurrentDb")))
CurrentDb= Request.QueryString( "CurrentDb");
if( !isDef( Tbl)&amp;&amp; isDef( DbName)&amp;&amp; DbName+ ""== CurrentDb+ ""&amp;&amp; isDef( TbId)){
Rs= Conn.Execute(
"select O.name, U.name"+
" from "+ DbName+ ".dbo.sysobjects O, "+
DbName+ ".dbo.sysusers U"+
" where O.id="+ TbId+ " and U.uid=O.uid"
);
TbName= Rs( 0);
TbOwner= Rs( 1);
Response.Write(
"<a href='TbStru.asp?Tbl=["+ DbName+ "].["+ TbOwner+ "].["+ TbName+ "]&amp;TbId="+ TbId+
"&amp;DbName="+ DbName+ "' target="_blank">"+
"<font color="white"><b>"+ DbName+ "."+ TbOwner+ "."+ TbName+ "</b></font></a>\n"
);
Response.Write( "<br/>\n");
Rs= Conn.Execute(
"select C.name, T.name, C.length, C.xprec, C.xscale,"+
" C.colstat, C.isnullable,"+
" case when C.autoval is null then 0 else 1 end,"+
" SC.text, "+
"( select CForgin.name+ ' of '+ '<a "+="" ""="" "dbname="+ DbName+ " &currentdb="+ DbName+
" &tbid="+ Convert( varchar, Sr.rkeyid)+ " &tbname="+ O.name+ " href='"TbStru.asp?"+'><b>'+ O.name+ '</b></a>'"+
" from "+ DbName+ ".dbo.sysreferences Sr,"+
DbName+ ".dbo.sysobjects O,"+
DbName+ ".dbo.syscolumns CForgin"+
" where Sr.fkeyid="+ TbId+ " and Sr.fkey1=C.colid and Sr.rkeyid=O.id"+
" and CForgin.id=O.id and CForgin.colid=Sr.rkey1"+
") from "+ DbName+ ".dbo.syscolumns C, "+
DbName+ ".dbo.systypes T, "+
DbName+ ".dbo.syscomments SC "+
"where C.id="+ TbId+ " and C.xtype=T.xusertype and C.cdefault*=SC.id "+
"order by C.colid"
);

 1<input name="DbName" type="hidden" value="```
 2=DbName
 3```"/>
 4<input name="CurrentDb" type="hidden" value="```
 5=DbName
 6```"/>
 7<input name="TbId" type="hidden" value="```
 8=TbId
 9```"/>
10<table bgcolor="#cfcfcf" border="1" bordercolor="#5f5f5f" cellpadding="3" cellspacing="0">
11<tr =trcolor="" ```="" bgcolor="```"><th>Nn</th><th>Name</th><th>Type</th><th>length</th>
12<th>precision</th><th>scale</th><th>default value</th><th>properties</th><th>relation</th></tr>   

for( TrColor= '', i= 1; !Rs.EOF; i++, Rs.MoveNext()){
if( trColor== trColor1) trColor= trColor2;
else trColor= trColor1;

1<tr =trcolor="" ```="" bgcolor="```">
2<td =trcolor2="" ```="" align="right" bgcolor="```"><b>```
3=i
4```</b></td><td>    

=Rs( 0)

1<td align="right"> ```
2=Rs( 1)
3```</td>
4<td align="right"> ```
5=Rs( 2)
6```</td>   

if( Rs( 1)== 'numeric'|| Rs( 1)== 'decimal'){
prec= Rs( 3);
scale= Rs( 4);
} else prec= scale= ' ';
colstat= "";
if( Rs( 7)== 1) colstat+= ", Identity";
if( Rs( 5)== 1) colstat+= ", Primary Key";
if( Rs( 6)== 1) colstat+= ", Nullable";
if( colstat== "")
colstat= " ";
else
colstat= colstat.substring( 2);
cdefault= Rs( 8);
if( !isDef( cdefault)) cdefault= " ";
else {
cdefault= ""+ cdefault;
cdefault= cdefault.substring( 1, cdefault.length- 1);
}
foreign= Rs( 9);
if( !isDef( foreign)) foreign= " ";

 1<td align="right">```
 2=prec
 3```</td><td>```
 4=scale
 5```</td>
 6<td>```
 7=cdefault
 8```</td><td>```
 9=colstat
10```</td><td>```
11=foreign
12```</td>
13</tr>   

}

1</table>   

}
if( isDef( Tbl)){
Response.Write( "<font color="white"><b>"+ Tbl+ "</b></font><br/>\n");
Rs= Conn.Execute( "select count( *) from "+ Tbl);
RecordCount= Rs( 0);
Response.Write( "<font color="white">"+ RecordCount+ " Records total</font><br/>");
Rs= Conn.Execute( "select * from "+ Tbl);
if( !isDef( Row))
Row= 0;
if( !Rs.EOF)
Rs.Move( Row);

1<table bgcolor="#9fbfcf" border="1" cellpadding="1" cellspacing="0"><tr bgcolor="#cfcfcf"><th>N/n</th>   

var ColCount= Rs.Fields.Count;
for( i1= 0; i1&lt; ColCount; i1++)
Response.Write( "<th>"+ Rs.Fields( i1).Name+ "</th>");
Response.Write( "</tr>\n");
Prev= Row- PgSize;
if( Prev&lt; 0&amp;&amp; Row&gt; 0) Prev= 0;
for( i= Row+ 1, Cntr= 0;
Cntr&lt; PgSize&amp;&amp; !Rs.EOF;
Rs.MoveNext(), i++, Cntr++
){
if( trColor== trColor1) trColor= trColor2;
else trColor= trColor1;

1<tr =trcolor="" ```="" bgcolor="```">
2<td =trcolor2="" ```="" align="right" bgcolor="```"><b>```
3=i
4```</b></td>   

for( i1= 0; i1&lt; ColCount; i1++)
Response.Write( "<td>"+ Rs.Fields( i1).Value+ "</td>");
Response.Write( "</tr>\n");
}
if( Cntr&gt; 0){
Response.Write( "<tr bgcolor="#cfcfcf"><th>N/n</th>");
for( i1= 0; i1&lt; ColCount; i1++)
Response.Write( "<th>"+ Rs.Fields( i1).Name+ "</th>");
Response.Write( "</tr>\n");
}
Response.Write( "</table>\n<table border="0" width="50%">");
if( Prev&gt;= 0){
QS= new String( Request.QueryString);
Prev= QS.substring( 0, QS.lastIndexOf( "=")+ 1)+ Prev;
Response.Write( "<td><a href='TbStru.asp?"+ Prev+ "'><font color="white"><b>&lt;&lt; Previous</b></font></a>   </td>\n");
}
if( !Rs.EOF){
QS= new String( Request.QueryString);
if( QS.lastIndexOf( "&amp;Row=")+ 1)
Next= QS.substring( 0, QS.lastIndexOf( "=")+ 1)+ ( i- 1);
else
Next= QS+ "&amp;Row="+ ( i- 1);
Response.Write( "<td align="right"><a href='TbStru.asp?"+ Next+ "'><font color="white"><b>Next &gt;&gt;</b></font></a></td>\n");
}
Response.Write( "</table>");
}

Published At
Categories with Web编程
Tagged with
comments powered by Disqus