C# / SQL Server Data Documentation By Robbe Morris

C# / SQL Server Data Documentation

By Robbe D. Morris

Printer Friendly Version

Robbe & Melisa Morris

Some of us don't have access to a nice tool for database documentation especially for code review purposes. We also don't always have the time to plug database specs into a special documenation tool. With that in mind, I put this C# sample together to provide the current look at key areas of a SQL Server 2000 database for code reviews and performance enhancement reviews from a developers prospective. Kind of a poor man's ERWIN (a very poor man). Some of those key areas are table structures, indexes, and stored procedures. In many cases, database constraints and various other settings are important but are often reviewed by our DBA's prior to production.

There are numerous ways that I could have gone with this. SQLDMO provides a ton of information via COM interop. ADO.NET also provides a nice view of these same areas. I chose to go right at SQL Server itself to ensure that I wasn't missing anything and give me a practical assignment as part of the training for .NET. Recently, I've gone back and spent a great deal of time re-familiarizing myself with DBA oriented topics via the following two books by Ken Henderson. If you do any development in SQL Server, you really ought to have a copy of these.

The Guru's Guide To SQL Server Stored Procedures, XML, and HTML

The Guru's Guide To Transact-SQL

In the sample C# code below, you'll see a few special system stored procedures: sp_tables, sp_columns , sp_stored_procedures, and sp_helptext. Each returning a list of tables, columns, stored procedures, and the text for stored procedures respectively. You'll also see a query I took straight out of The Guru's Guide To Transact-SQL to list all of the indexes for a specific table. I purposely left out quite a bit of information returned by these procedures. Only information relevant to my code review were included. However, I would encourage you to use SQL Server's Query Analyzer and review their outputs.

Feel free to take the following code sample and make any adjustments you need. For now, just copy and paste the contents into a .aspx page, set the database variables accordingly, and run the sample. If you have any questions for comments, please include them in our forums via the link below the code sample.

Sample Code

 
 <%@ 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>



	Robbe has been a Microsoft MVP in C# since 2004. He is also the co-founder of EggHeadCafe which provides .NET articles, book reviews, software reviews, and software download and purchase advice.