Using a Wiki for knowledge sharing and SQL Server database documentation

<%

' Examples of custom build macros.
'
' When you create a new macro add the letter P to the name of
' the sub for each parameter you define. A macro can take at
' most 2 parameters.
'
' A macro should return the value that is supposed to be
' substituted in the text by setting the global variable
' gMacroReturn.

' For each macro you add below, you must add it's name to the
' return value of this function. Seperate the names by the
' pipe (|) character.
'
' If you want to redefine all available macros set the
' variable gMacros (see also owpatterns.asp).
Function MyMacroPatterns
    If cEmbeddedMode = 0 Then
        'gMacros = "BR|RecentChanges|TitleSearch|FullSearch|TextSearch|TableOfContents|WordIndex|TitleIndex|GoTo|RandomPage|InterWiki|SystemInfo|Include|PageCount|UserPreferences|Icon|Anchor|Date|Time|DateTime|Syndicate|Aggregate|Footnote"
        MyMacroPatterns = "Glossary|Files|Pagechanged|Image|ServerDetails|ProgressBar|DatabaseTable|"
    End If
End Function


' code by Dan Rawsthorne
' taken from http://openwiki.com/?OpenWiki/Suggestions
Sub MacroGlossaryP(pParams)
    gMacroReturn = GetGlossaryP(pParams)
End Sub

Public Function GetGlossaryP(pPattern)
    Dim vList, vCount, i, vResult
    Set vList = gNamespace.FullSearch(pPattern, False)
    vCount = vList.Count - 1
    For i = 0 To vCount
        vResult = vResult & vList.ElementAt(i).ToXML(False)
    Next
    GetGlossaryP = "<ow:titleindex>" & vResult & "</ow:titleindex>"
End Function


' original code by Leopold Faschalek
' modified by Dave Cantrell
' modified by Laurens Pit
' taken from http://openwiki.com/?OpenWiki/Suggestions
Sub MacroFilesP(pPath)
    Call MacroFilesPP(pPath, "[\s\S]*")
End Sub

Sub MacroFilesPP( pPath, pWild )
  dim oFso, oFolder, oFiles, oFile
  Set oFso = Server.CreateObject( "Scripting.FileSystemObject" )
  If oFso.FolderExists( pPath ) then
    Set oFolder = oFso.GetFolder( pPath )
    Set oFiles  = oFolder.Files
    'parses path and converts to UNC path so files can be retrieved from server across network
    dim sLocalPathPrefix  : sLocalPathPrefix = Left( oFolder.Path, 1 )
    dim sUncPath
    sUncPath = "file:\\\" & pPath & "\"
    'sUncPath = "\\mymachine\" & Lcase( sLocalPathPrefix ) & "$" & Right( oFolder.Path, Len( oFolder.Path ) - 2 ) & "\"  '"
    'sUncPath = "http:\\www.mysite.com\" & Lcase( sLocalPathPrefix ) & "$" & Right( oFolder.Path, Len( oFolder.Path ) - 2 ) & "\"  '"
    gMacroReturn = "<b>" & sUncPath & "</b><ul>"
    For each oFile in oFiles
      If m( oFile.Name, pWild, false, true ) then
        gMacroReturn = gMacroReturn & "<li><a href='" & sUncPath & oFile.Name & "' target='_blank'>" & oFile.Name & "</a></li>"
      End If
    Next
    gMacroReturn = gMacroReturn & "</ul>"
  Else
    gMacroReturn = "<ow:error>error in path: " & pPath & "</ow:error>"
  End If
  Set oFile   = Nothing
  Set oFiles  = Nothing
  Set oFolder = Nothing
  Set oFso    = Nothing

  ' prevent the caching of pages wherein this macro is used
  cCacheXML = False
End Sub

Sub MacroPagechangedP(pParam)
    Dim vPage, vTimestamp
    Set vPage = gNamespace.GetPage(pParam, 0, False, False)
    If vPage.Exists Then
        vTimestamp = vPage.GetLastChange().Timestamp()
        gMacroReturn = FormatDate(vTimestamp)
    End If
End Sub

Sub MacroImageP(pParam)
    gMacroReturn = "<div style=""width:100%;overflow-x:auto""><img src=""images/" & pParam & """ border=""0"" alt=""" & pParam & """/></div>"
End Sub


Sub MacroProgressBarPP(pWidth, pPercent)
  if pPercent<0 or pPercent>100 then
    gMacroReturn = "<ow:error>ProgressBar error: pPercent must be [0..100]</ow:error>"
  else
    gMacroReturn= _
      "<ow:progressbar pbWidth=""" & pWidth & """ pbPercent=""" & pPercent & """ pbPercentLeft=""" & 100-pPercent & """ />"
  end if
end sub

Function FormatXMLString(ByVal sString)
    Dim i, c

    If Not (IsNull(sString)) Then
        sString = Replace(sString, "&", "&amp;")
        sString = Replace(sString, """", "&quot;")
        sString = Replace(sString, ">", "&gt;")
        sString = Replace(sString, "<", "&lt;")
    End If
    
    For i = 1 to Len(sString)
	c = Mid(sString, i, 1)
	if Asc(c) < 32 Then
		c = "*"
	End If
   	FormatXMLString = FormatXMLString & c
    Next
End Function

Sub MacroDatabaseTableP(pParam)

	Dim oConn
	Dim oCmd
	Dim oParam
	
	Dim oRsColumns
	Dim oRsPrimary
	Dim oRsData
	
    Set oConn = Server.CreateObject("ADODB.Connection")
    
    oConn.Open OPENWIKI_DB
    oConn.CursorLocation = adUseClient
    
    Set oCmd = Server.CreateObject("ADODB.Command")
    
    Set oCmd.ActiveConnection = oConn
    oCmd.CommandText = "wikidbschema"
    oCmd.CommandType = adCmdStoredProc
    
    Set oParam = Server.CreateObject("ADODB.Parameter")
    With oParam
        .Name = "tablename"
        .Type = 8 ' adBSTR
        .Size = 255
        .Direction = 1 'adParamInput
        .Value = pParam
    End With
    
    Call oCmd.Parameters.Append(oParam)
    
    'If Err.Number <> 0 then
	'	Response.Write (Err.Description)
	'End If
    
    Set oRsColumns = oCmd.Execute
    
    If oRsColumns.EOF = True Then
		gMacroReturn = FormatXMLString("Table '" & pParam & "' does not exist.")
		Exit Sub
	End If
    
    gMacroReturn = gMacroReturn & "<table><tr style=""font:8pt""><td> <b>Column</b> </td><td> <b>Data Type</b> </td><td> <b><nobr>Allow NULLs</nobr></b> </td><td><b>Default</b></td></tr>"
    
    Do Until oRsColumns.EOF
		gMacroReturn = gMacroReturn & "<tr valign=""TOP"" style=""font:8pt"">"
    
		gMacroReturn = gMacroReturn & "<td>" & oRsColumns("column_name") & "</td>"
    
    	gMacroReturn = gMacroReturn & "<td><nobr>" & oRsColumns("data_type") & " (" & oRsColumns("length") & ")"
		gMacroReturn = gMacroReturn & "</nobr></td>"

	    ' Allows NULLs
		If oRsColumns("is_nullable") = "Y" Then
			gMacroReturn = gMacroReturn & "<td align=""MIDDLE""><img src=""images/wiki/tick.gif""></img></td>"
		Else
			gMacroReturn = gMacroReturn & "<td align=""MIDDLE""><img src=""images/wiki/notick.gif""></img></td>"
		End If
	
		If IsNull(oRsColumns("default_value")) Then
			gMacroReturn = gMacroReturn & "<td align=""MIDDLE"">&#160;</td>"
		Else
			gMacroReturn = gMacroReturn & "<td align=""MIDDLE""><nobr>" & oRsColumns("default_value") & "</nobr></td>"
		End if
	
		gMacroReturn = gMacroReturn & "</tr>"
    
        oRsColumns.MoveNext
    Loop
    gMacroReturn = gMacroReturn & "</table>"
    
    
    Set oRsPrimary = oRsColumns.NextRecordset
    
    If oRsPrimary.EOF Then
       gMacroReturn = gMacroReturn & "<b><font color=""RED"">Warning: This table does not have a primary key index defined.</font></b><br/><br/>"
    End If
    
    gMacroReturn = gMacroReturn & "<br/><b>Primary Key</b><ul>"
    Do Until oRsPrimary.EOF
        gMacroReturn = gMacroReturn & "<li><img src=""images/wiki/key.gif"" align=""middle""></img>&#160;<u><b>" & oRsPrimary("column_name") & "</b></u></li>"
        oRsPrimary.MoveNext
    Loop
    gMacroReturn = gMacroReturn & "</ul>"
    
    
    Dim vQuery
    Dim vRs
    Dim i
    
    ' Get secondary indexes
    vQuery = "SELECT index_name = i.name, col_name = c.name, i.indid FROM sysindexkeys k, sysindexes i, syscolumns c WHERE i.id = OBJECT_ID('" & pParam & "') AND i.name NOT LIKE 'PK_%' AND i.name NOT LIKE '_WA_Sys%' AND k.id = i.id AND k.indid = i.indid AND c.id = i.id AND c.colid = k.colid ORDER BY i.indid, keyno"
    
    Set vRs = Server.CreateObject("ADODB.Recordset")
    vRs.Open vQuery, oConn
    
    gMacroReturn = gMacroReturn & "<br/><b>Secondary Indexes:</b><ul>"
    i = 0
    Do While Not vRs.EOF
		
		If i <> vRs("indid") Then
			If i > 0 Then
				gMacroReturn = gMacroReturn & "</li>"
			End If
			gMacroReturn = gMacroReturn & "<li><b>" & vRs("index_name") & "</b>"
			i = vRs("indid")
			gMacroReturn = gMacroReturn & ": " & vRs("col_name")
		Else
			gMacroReturn = gMacroReturn & ", " & vRs("col_name")
		End If
		vRs.MoveNext
		
    Loop
    
    If i = 0 Then
		gMacroReturn = gMacroReturn & "</ul>None<br/>"
    Else
		gMacroReturn = gMacroReturn & "</li></ul>"
    End If
    vRs.Close
    
    
    ' Get stored proc dependencies
    Set vRs = Server.CreateObject("ADODB.Recordset")
    
    vQuery = "SELECT name FROM sysobjects WHERE xtype = 'P' AND id IN (select id from sysdepends where depid = OBJECT_ID('" & pParam & "')) ORDER BY name"
    vRs.Open vQuery, oConn ', adOpenForwardOnly
    
    gMacroReturn = gMacroReturn & "<br/><b>Stored procedures that access this table:</b><ul>"
    i = 0
    
    Do While Not vRS.EOF
		gMacroReturn = gMacroReturn & "<li>" & vRs("name") & """>" & vRs("name") & "</li>"
		vRs.MoveNext
		i = i + 1
    Loop
	
	If i = 0 Then
		gMacroReturn = gMacroReturn & "</ul>None<br/>"
    Else
		gMacroReturn = gMacroReturn & "</ul>"
	End If
    vRs.Close

	gMacroReturn = gMacroReturn & "<br/><hr/>"

	gMacroReturn = gMacroReturn & "<br/>"
	
	Dim vRs2
	vQuery = "SELECT TOP 5 * FROM [" & pParam & "]"
	Set vRs2 = Server.CreateObject("ADODB.Recordset")
    vRs2.Open vQuery, oConn, adOpenForwardOnly
	
	If Not vRs2.EOF Then
		If vRs2.RecordCount > 0 Then
			
			gMacroReturn = gMacroReturn & "<table><tr>"
			
			For i = 0 To vRs2.Fields.Count - 1
				gMacroReturn = gMacroReturn & "<th>" & vRs2.Fields(i).Name & "</th>"
			Next
			gMacroReturn = gMacroReturn & "</tr>"
			
			Do Until vRs2.EOF
			
				gMacroReturn = gMacroReturn & "<tr>"
				
				For i = 0 To vRs2.Fields.Count - 1
				
					If IsNull(vRs2(i)) Then
						gMacroReturn = gMacroReturn & "<td>NULL</td>"
					Else
						If vRs2(i).Type = "text" Then
							gMacroReturn = gMacroReturn & "<td>&lt;Binary&gt;</td>"
						ElseIf vRs2(i) = "" Then
							gMacroReturn = gMacroReturn & "<td>&#160;</td>"		
						Else
							gMacroReturn = gMacroReturn & "<td>" & FormatXMLString(vRs2(i).value) & "</td>"
						End If
					End If
					
				Next
				
				gMacroReturn = gMacroReturn & "</tr>"
				
				vRs2.MoveNext
			Loop
		    
		    gMacroReturn = gMacroReturn & "<tr><td colspan=""" & vRs2.Fields.Count & """>...</td></tr>" 
		    
		    gMacroReturn = gMacroReturn & "</table>"		    
		Else
			gMacroReturn = gMacroReturn & FormatXMLString("Table '" & pParam & "' contains no data.")
		End If
	Else
	    gMacroReturn = gMacroReturn & FormatXMLString("Table '" & pParam & "' contains no data.")
	End If


End Sub
%>