Click here to Skip to main content
Click here to Skip to main content
 
Add your own
alternative version

Using a Wiki for knowledge sharing and SQL Server database documentation

, 7 Feb 2004
This article describes using a Wiki for knowledge sharing and database schema documentation.
wiki_src.zip
attachments
images
Help
edit_comment.gif
Thumbs.db
Wiki
DatabaseSchema.gif
DeprecatedBanner.gif
FindPage.gif
HelpBanner.gif
Key.gif
NoTick.gif
Thumbs.db
Tick.gif
UserPreferences.gif
ow
css
images
banner_logo.gif
banner_search.gif
banner_title.gif
breadcrumb_divider.gif
dashed_line.gif
delico.gif
edit_icon.gif
find_icon.gif
grey_box_left.gif
grey_box_line.gif
grey_box_right.gif
hidden_off.gif
hidden_on.gif
icons
doc
asp.gif
avi.gif
bmp.gif
chm.gif
doc.gif
empty.gif
gif.gif
gz.gif
htm.gif
html.gif
jpeg.gif
jpg.gif
mov.gif
mp3.gif
mpeg.gif
mpg.gif
pdf.gif
png.gif
ppt.gif
Thumbs.db
txt.gif
wav.gif
xls.gif
xml.gif
xsl.gif
zip.gif
emoticon-8.gif
emoticon-ambivalent.gif
emoticon-b.gif
emoticon-c.gif
emoticon-cat.gif
emoticon-d.gif
emoticon-e.gif
emoticon-f.gif
emoticon-g.gif
emoticon-h.gif
emoticon-i.gif
emoticon-k.gif
emoticon-l.gif
emoticon-laugh.gif
emoticon-m.gif
emoticon-n.gif
emoticon-p.gif
emoticon-s.gif
emoticon-sad.gif
emoticon-smile.gif
emoticon-star.gif
emoticon-surprised.gif
emoticon-t.gif
emoticon-tongue-in-cheek.gif
emoticon-u.gif
emoticon-unsure.gif
emoticon-wink.gif
emoticon-wink_old.gif
emoticon-y.gif
icon-error.gif
icon-info.gif
icon-warning.gif
Thumbs.db
wiki-ftp.gif
wiki-http.gif
wiki-mailto.gif
wiki-news.gif
wiki-telnet.gif
wiki-wiki.gif
icon_edit.gif
icon_print.gif
icon_revisions.gif
logo.gif
logo_aggregator.gif
logo_tiny.gif
popup.gif
popup2.gif
poweredby.gif
revs.gif
rss_button.gif
Thumbs.db
undo.gif
valid-css.gif
valid-xhtml10.gif
xml.gif
my
xsl
mystyle.xsl
ow.xsl
owattach.xsl
owinc.xsl
owrss09.xsl
owrss091.xsl
owrss10.xsl
owrss10aggr.xsl
owrss10export.xsl
owscriptingnews.xsl
xmldisplay.xsl
data
wiki.mdb
wikidb.mdf
<%

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

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Author

Jonathan Hodgson
Web Developer
United Kingdom United Kingdom
Jonathan Hodgson works as Software Developer in London, UK.
 
He started programming in the '80s on a trusty 48k Spectrum before moving to PC development in the early 90s. During the working week most of his time is spent involved in application development both Windows and Web-based; .NET, C#, ASP.NET, SQL Server.
 
He is a Microsoft Certified Software Developer (MCSD) and MCP for developing web applications using ASP.NET in C# and is always looking for new projects and challenges to work on.
 
http://www.jonathanhodgson.co.uk/

| Advertise | Privacy | Mobile
Web03 | 2.8.141029.1 | Last Updated 8 Feb 2004
Article Copyright 2004 by Jonathan Hodgson
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid