<%@ Page Language="C#" Debug="true" %>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script Language="C#" runat="server" Debug="true">
protected void Page_Load(object sender, EventArgs e) { WritePage(); }
public void RW(string sVal)
{ Response.Write(sVal +'\n'); }
public void RWROW(string sVal)
{ Response.Write(" <tr> <td align="left" class=BodyText8>" + sVal + " </td> </tr>\n"); }
public void WritePage()
{
string sUser="your database user";
string sPwd="your password";
string sDB="DATABASE NAME";
string sConStr = "Data Source=(local);User ID=" + sUser + ";Password=" + sPwd + ";Initial Catalog=" + sDB ;
RW(" <html> <TITLE>SQL Server Documentation </TITLE>");
RW(" <STYLE>");
RW("BODY { scrollbar-base-color: #858BFD ; scrollbar-arrow-color: #FFFFFF;");
RW(" scrollbar-DarkShadow-Color: #FFFFFF; scrollbar-Track-Color: #FFFFFF; scrollbar-highlight-color:#CCCCCC }");
RW(".BodyText8 { font-family:verdana,arial,helvetica; color:#000000; ");
RW("font-size:8pt; text-decoration:none; font-style:normal; background-color:#FFFFFF; }");
RW(".BodyText10 { font-family:verdana,arial,helvetica; color:#000000; ");
RW("font-size:10pt; text-decoration:none; font-style:normal; background-color:#FFFFFF;}");
RW(".BodyColor8 { font-family:verdana,arial,helvetica; color:#858BFD ; ");
RW("font-size:8pt; text-decoration:none; font-style:normal; background-color:#FFFFFF; }");
RW(".BodyColor10 { font-family:verdana,arial,helvetica; color:#858BFD ; ");
RW("font-size:10pt; text-decoration:none; font-style:normal; background-color:#FFFFFF; }");
RW(" </STYLE>");
RW(" <BODY bottomMargin=0 bgColor=#ffffff leftMargin=15 topMargin=20>" );
RW(" <TABLE border="0" WIDTH=600 align="left" cellspacing="2" cellpadding=1>");
RW(" <tr> <td align="left" class=BodyText10><b>SQL Server Documention: </b> " + sDB + " </td> </tr>");
RWROW(" <hr size=1 color=#858BFD noshade>");
RW(" <TR> <TD align="left">");
WriteAllTables(sConStr);
RW("</td></tr>");
RWROW("<BR>");
RWROW("<BR>");
RW("<TR><TD align="left">");
WriteAllProcedures(sConStr);
RW("</td></tr>");
RWROW("<BR>");
RWROW("<hr size=1 color=#858BFD noshade>");
RW("</TABLE></BODY></HTML>");
}
public int WriteAllTables(string sConStr)
{
int nRetVal=0;
int nStrip=0;
string sTbl="";
string sTblType="";
string sTblName="Tables";
string sSQL = "";
DataSet oDS = new DataSet();
SqlConnection oConn = new SqlConnection();
sSQL = "sp_tables";
oConn.ConnectionString = sConStr;
oConn.Open();
nRetVal = GetDS(oConn,sSQL,oDS,sTblName);
oConn.Close();
RW("<table border="0" width='100%' cellspacing="2" cellpadding="2">");
RW("<tr><td align="left" class=BodyColor10><b>Tables</b></td></tr>");
RWROW("<br>");
foreach(DataRow row in oDS.Tables[sTblName].Rows)
{
sTbl = row[2].ToString();
sTblType = row[3].ToString();
if ((sTblType == "TABLE") && (sTbl.Substring(0,2) !="dt"))
{
WriteTable(sConStr,sTbl);
RWROW("<br>");
RWROW("<br>");
}
}
RW("</TABLE>");
return 1;
}
public int WriteTable(string sConStr,string sName)
{
int nRetVal=0;
string sSQL = "";
DataSet oDS = new DataSet();
SqlConnection oConn = new SqlConnection();
sSQL = "sp_columns [" + sName + "]";
oConn.ConnectionString = sConStr;
oConn.Open();
nRetVal = GetDS(oConn,sSQL,oDS,sName);
oConn.Close();
RWROW("<b>" + sName + "</b>");
RWROW("<br>");
try
{
RW("<TR><TD align="left">");
RW("<table border=1 bordercolor=#858BFD width='50%' align="left" cellspacing=1 cellpadding=1>");
RW("<tr bordercolor=#FFFFFF>");
RW("<td align="left" class=BodyText8><b>Column</b></td>");
RW("<td align="left" class=BodyText8><b>Data Type</b></td>");
RW("<td align="left" class=BodyText8><b>Length</b></td>");
RW("<td align="left" class=BodyText8><b>Nullable</b></td>");
RW("</tr>");
foreach(DataRow row in oDS.Tables[sName].Rows)
{
RW("<tr bordercolor=#FFFFFF>");
RW("<td align="left" class=BodyText8>" + row[3].ToString() + "</td>");
RW("<td align="left" class=BodyText8>" + row[5].ToString() + "</td>");
RW("<td align="left" class=BodyText8>" + row[7].ToString() + "</td>");
RW("<td align="left" class=BodyText8>" + row[17].ToString() + "</td>");
RW("</tr>");
}
RW("</table></TD></tr>");
RWROW("<br>");
WriteTableIndexes(sConStr,sName);
}
catch (Exception e) { nRetVal = 2; RWROW(e.Message); }
return 1;
}
public int WriteTableIndexes(string sConStr,string sName)
{
int nRetVal=0;
string sSQL = "";
DataSet oDS = new DataSet();
SqlConnection oConn = new SqlConnection();
sSQL = " select IndexName=CAST(name as varchar(50)), ";
sSQL += " KeyName=CAST(INDEX_COL(OBJECT_NAME(id),indid,1) as VARCHAR(30)), ";
sSQL += " 'Clustered'=CASE INDEXPROPERTY(id,name,'IsClustered') WHEN 1 then 'Yes' ELSE 'No' END, ";
sSQL += " 'Unique'=CASE INDEXPROPERTY(id,name,'IsUnique') WHEN 1 then 'Yes' ELSE 'No' END ";
sSQL += " from sysindexes ";
sSQL += " where OBJECT_NAME(id) = '" + sName + "'";
oConn.ConnectionString = sConStr;
oConn.Open();
nRetVal = GetDS(oConn,sSQL,oDS,sName);
oConn.Close();
RWROW("<b>" + sName + " Indexes</b>");
RWROW("<br>");
try
{
RW("<TR><TD align="left">");
RW("<table border=1 bordercolor=#858BFD width='50%' align="left" cellspacing=1 cellpadding=1>");
RW("<tr bordercolor=#FFFFFF>");
RW("<td align="left" class=BodyText8><b>Index Name</b></td>");
RW("<td align="left" class=BodyText8><b>Key Name</b></td>");
RW("<td align="center" class=BodyText8><b>Clustered?</b></td>");
RW("<td align="center" class=BodyText8><b>Unique?</b></td>");
RW("</tr>");
foreach(DataRow row in oDS.Tables[sName].Rows)
{
RW("<tr bordercolor=#FFFFFF>");
RW("<td align="left" class=BodyText8>" + row[0].ToString() + "</td>");
RW("<td align="left" class=BodyText8>" + row[1].ToString() + "</td>");
RW("<td align="center" class=BodyText8>" + row[2].ToString() + "</td>");
RW("<td align="center" class=BodyText8>" + row[3].ToString() + "</td>");
RW("</tr>");
}
RW("</table></TD></tr>");
}
catch (Exception e) { nRetVal = 2; RWROW(e.Message); }
return 1;
}
public int WriteAllProcedures(string sConStr)
{
int nRetVal=0;
int nStrip=0;
string sProc="";
string sTblName="StoredProcedures";
string sSQL = "";
DataSet oDS = new DataSet();
SqlConnection oConn = new SqlConnection();
sSQL = "sp_stored_procedures";
oConn.ConnectionString = sConStr;
oConn.Open();
nRetVal = GetDS(oConn,sSQL,oDS,sTblName);
oConn.Close();
RW("<table border="0" width='100%' cellspacing="2" cellpadding="2">");
RW("<tr><td align="left" class=BodyColor10><b>Stored Procedures</b></td></tr>");
RWROW("<br>");
foreach(DataRow row in oDS.Tables[sTblName].Rows)
{
sProc = row[2].ToString();
nStrip = sProc.IndexOf(";",0,sProc.Length);
sProc = sProc.Substring(0,nStrip);
if (sProc.Substring(0,2) != "dt")
{
WriteStoredProcedure(sConStr,sProc);
RWROW("<br>");
RWROW("<br>");
}
}
RW("</TABLE>");
return 1;
}
public int WriteStoredProcedure(string sConStr,string sProcName)
{
int nRetVal=0;
string sSQL = "";
DataSet oDS = new DataSet();
SqlConnection oConn = new SqlConnection();
sSQL = "sp_helptext [" + sProcName + "]";
oConn.ConnectionString = sConStr;
oConn.Open();
nRetVal = GetDS(oConn,sSQL,oDS,sProcName);
oConn.Close();
RWROW("<b>" + sProcName + "</b>");
RWROW("<br>");
try
{
RW("<TR><TD align="left"><PRE class=BodyText8>");
foreach(DataRow row in oDS.Tables[sProcName].Rows)
{
RW(row[0].ToString());
}
RW("</pre></TD></tr>");
}
catch (Exception e) { nRetVal = 2; RWROW(e.Message); }
return 1;
}
public int GetDS(SqlConnection oConn,string sSQL, DataSet oRetDS,string sTblName)
{
int nRetVal = 0;
try
{
SqlDataAdapter oDA = new SqlDataAdapter(sSQL,oConn);
oDA.Fill(oRetDS,sTblName);
nRetVal = 1;
}
catch (Exception e) { nRetVal = 2; RW(e.Message); }
return nRetVal;
}
</script>
|