HTML DB Documentor & Script Generator

This script generates HTML documentation or Scripts for all the accessible DBs in a SQL instance.

 
 
 
 
 
4.3 Star
(3)
Add to favorites
Databases
6/24/2011
E-mail Twitter del.icio.us Digg Facebook
  • Schema names using {domain}\{user}
    1 Posts | Last post November 22, 2013
    • First thank you so much for the script, it's great. I came across something that I don't think I would have thought of: the schema name being based off of a Windows Security Login name .  For example   [MyDomain\Mylogin] . 
      
      In function buildLinkList, I suggest using .replace('\','_') on all classes of objects, and not just the User class, to avoid the "\" issue. This may be overkill, but I have a "why not" attitude when it comes to avoiding an error/issue it can be avoided.
      
      Thanks Again, Shankar
      
      Erik Grob, MCITP, MCDBA
  • Exception calling script with "1" argument: script failed for stored procedure
    1 Posts | Last post August 10, 2012
    • The PS ran great.  I am unsure as to why it was not able to record one stored procedure as the login used is the default sysadmin role as well as the dbo.
  • Warning No server List file specified!!!
    2 Posts | Last post August 10, 2012
    • I receive the above message when I run the script.  Any ideas as to why?
    • I did not realize that I needed to create a file named ServerList.txt and put the server name inside.  The program works awesomely!  Thank you so much!  You have saved me so much time.
  • Script error
    2 Posts | Last post October 12, 2011
    • Hi Shankar, 
      
      It would've been a big help if i had this script run without error. 
      Do you have any idea why I seem to have trouble with your script with this error: 
      
      PS C:\dbahome\PSHome> .\SQLDoc_v1.ps1 C:\dbahome\ServerList.txt Docs
      Starting script execution at : 10/11/2011 11:44:49
      Attempting to retrieve list for processing !!!
      Successfully retrieved list for processing !!!
      Note: The generated documentation will be stored in C:\Users\Administrator\database_documentation\ folder.
      Documented Server info
      Started documenting
      Documented DB info
      Index operation failed; the array index evaluated to null.
      At C:\dbahome\PSHome\SQLDoc_v1.ps1:86 char:39
      +     $schemata = $sql_server.Databases[ <<<< $db_name].Schemas;
          + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
          + FullyQualifiedErrorId : NullArrayIndex
      
      Documented schemata
      Index operation failed; the array index evaluated to null.
      At C:\dbahome\PSHome\SQLDoc_v1.ps1:95 char:37
      +     $tables = $sql_server.Databases[ <<<< $db_name].Tables | Where-Object {$_.IsSystemObject -eq $false};
          + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
          + FullyQualifiedErrorId : NullArrayIndex
      
      Documented tables
      Index operation failed; the array index evaluated to null.
      At C:\dbahome\PSHome\SQLDoc_v1.ps1:122 char:36
      +     $views = $sql_server.Databases[ <<<< $db_name].Views | Where-Object {$_.IsSystemObject -eq $false};
          + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
          + FullyQualifiedErrorId : NullArrayIndex
      
      Documented views
      Index operation failed; the array index evaluated to null.
      At C:\dbahome\PSHome\SQLDoc_v1.ps1:104 char:36
      +     $procs = $sql_server.Databases[ <<<< $db_name].StoredProcedures | Where-Object {$_.IsSystemObject -eq $false
          + CategoryInfo          : InvalidOperation: (:) [], RuntimeException
          + FullyQualifiedErrorId : NullArrayIndex
      ......
    • Hi Nicolas,
               Looks like the credentials used may not have had the proper permissions as they were unable to retrieve the list of databases present in the instance.
      
      Can you please try using the 'sa' account or with an account that has the sysadmin server role granted?   
      
      
      Cheers,
      Shankar
  • Good
    7 Posts | Last post June 24, 2011
    • Good script.
      However, the HTML table is coming incorrect:
      
      TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} TH{background:#3b3131;color:snow;font-size:15px;border-width: 1px;padding: 10px;border-style: solid;border-color: black;} TD{background:#736F6E;color:snow;font-size:13px;border-width: 1px;padding: 10px;border-style: solid;border-color: black;} 
    • Resolved!
      
      To print correctly, put this tags in the variable $colstyl: 
      
      <STYLE type=text/css>
      </STYLE>
      
      Thanks
    • Hi Andre,
                Thanks for the feedback. I appreciate. 
      
      Can you please share where exactly you had faced the problem with HTML table? 
      
      I had tested the script output in IE 6/8 and Firefox 4 and the output was rendered properly.
      
      Also do you believe any other feature that can/needed to be added to this? Please let me know.
      
      
      Cheers,
      Shankar
      
    • I'm also facing this issue. I'm running this on a Win server 2008, with SQL Server 2008 R2, IE 8.
      The CSS codes as mentioned above is printed on the webpage, and the table with list of objects (parameters, columns, and so on) is not formatted.
      
      I also had to change the code as Andre Bozo suggested:
      
       $colstyl = "<STYLE type=text/css>   
                  TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} 
                  TH{background:#3b3131;color:snow;font-size:15px;border-width: 1px;padding: 10px;border-style: solid;border-color: black;} 
                  TD{background:#736F6E;color:snow;font-size:13px;border-width: 1px;padding: 10px;border-style: solid;border-color: black;} </STYLE>";    
      
      This must be done several places.
      
      One thing that I see in the generated HTML code, is that the converrto-html cmdlet adds <HTML> and <header> tags inside the <BODY> tag of the page, i don't know if this could be done cleaner?
      Here is a part of a generated html file:
      
      html>  
                   <head>  
                       <title>dbo.TestProc</title>  
                   </head>  
                   <body>  
                       <h1>dbo.TestProc</h1>  
                      <h2 style='font-size:15px;text-decoration:underline'>DESCRIPTION</h2>No MS_Description property on object.<h2 style='font-size:15px;text-decoration:underline'>Properties</h2><p style='line-spacing:120%'>Created Date: 23.08.2010 13:22:39<br>Last Modified On: 23.08.2010 13:25:42<br>Implementation Type: TransactSql<br>Schema: dbo<br>Owner :dbo<br></p><h2 style='font-size:15px;text-decoration:underline'>PARAMETERS</h2><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <STYLE type=text/css>   
                  TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} 
                  TH{background:#3b3131;color:snow;font-size:15px;border-width: 1px;padding [...code removed]
    • Hi,
      Thanks for your valuable feedback.Should have relaized with Andre's commnets itself.  Just noticed that the variable $colstyl in which I had given the style related attributes is empty. Not sure why it is happening. I even tried re-pasting the source code with the values intact. However, script center's application is removing the vlaues for the variable.
      
      Will try to find a workaround.
      
      Cheers,Shankar
    • Hi,
         Please use the eblow values for the $colstyl variable:
      
      $colstyl = "<style>
      			TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}
      			TH{background:#3b3131;color:snow;font-size:15px;border-width: 1px;padding: 10px;border-style: solid;border-color: black;}
      			TD{background:#736F6E;color:snow;font-size:13px;border-width: 1px;padding: 10px;border-style: solid;border-color: black;}</style>";	 
      
    • Hi,
       After struggling to find a solution to the Style issue, I have uploaded the source in the form of a ps1 file. Please use this for execution.
      
      Cheers,
      Shankar
  • Suggestion
    1 Posts | Last post June 22, 2011
    • A Suggestion would be to implement hyperlinks for dependencies. A stored procedure listed under a dependencies on a table, could be clickable...
      
      Thanks for the good work!