>Hi again Gentlefolk.
>
>What do you use (if anything) for documenting SQL Server DB, Stored Procs, Trigger, etc? Contract is coming to an end and I'll need to document what I've been doing for whomever takes over.
>
>Yeah, I know....documentation - what a concept!

Descriptions in the fields and tables themselves. I know that's what I'm looking for when I need to get anything from a database, so that's what I'm writing for myself. Even if it's only a "fk into othertable.somekey" - beats reading code to find the relationship. Also, for fields bound to combos with a short list of hardcoded options, I put "1 yes, 2 no, 3 maybe, 4 never, 5 n/a" and such. For FKs into the list-of-lists, it's "fk into TheBigList.somekey, listGroup 343". And I have a couple of routines to maintain that list (see code below - it generates the actual routine from what's already in the db), because it's easier to maintain a prg than to jump from table to table, one dialog per table at least (even Toad is not too good for this, and SSMS is outright awful).

For stored code, I guess you could introduce a leading comment with some special delimiters, so get the code and strextract() the comments.

LOCAL nTbl, cTbl, nFlds, cFld, lcFullFld, lcComment, afld[1], aTbl[1]

#DEFINE hCr	CHR(13)

h=sqlconnect("my connect string")

nTbl=SQLTABLES(h,"TABLE", "curTables")

lcCalls=""
lccode=""
SCAN
	cTbl=ALLTRIM(table_name)
	lcRoutine="Do_"+cTbl
	lc1Routine=GetCode(cTbl)
	IF NOT EMPTY(lc1Routine)
		lcCalls = lcCalls + lcRoutine+"()"+0h0d
		lccode = lccode + lc1Routine
	ENDIF
ENDSCAN

* lcOwnProcs contains textmerge delimiters, so keep it out of the textmerge to prevent complaints of recursion.
lcOwnProcs=OwnProcs()

SET DATE YMD
*-- TEXT BLOCK BEGIN
TEXT TO lcBig NOSHOW TEXTMERGE

#DEFINE hCr	CHR(13)
* the handle
h=goApp.h


<<lcCalls>>


<<lcCode>>

ENDTEXT
*-- TEXT BLOCK END

STRTOFILE(lcbig+lcOwnProcs, "tools\gen_commentsql.prg")




*-------------------------------------------------
PROCEDURE GetCode(tcTbl)
	LOCAL lcProc, lcTbl, i, lcRows, lcRow, aRows[1], lcComment, N, lcFullFld

	lcRows=""

	lcTablecomment=getTableDescr(tcTbl)
*[2010/01/29 10:22:02] ndragan - some labels had [] in them, so convert before it hurts the generated code.
	IF !EMPTY(lcTablecomment)
		lcTablecomment=CHRTRAN(lcTablecomment, "[]","()")
		N=ALINES(aRows, lcTablecomment,1+4)
		lcComment=""
		FOR i=1 TO N
			lcComment = lcComment + "["+aRows[i]+"]"+IIF(i<N, [+hcr+;]+0h0d0a0909, "")
		ENDFOR
		lcTablecomment=TEXTMERGE([	setTableDescription("<<tctbl>>",<<lccomment>>)])
	ENDIF

*-- text block begin
	TEXT to lcSql noshow textmerge
SELECT *
FROM fn_listextendedproperty(NULL, 'user', 'dbo', 'table', '<<tcTbl>>', 'column', default);
	ENDTEXT
*-- text block end
	nRet=SQLEXEC(h, lcsql, "crsCols")
	IF nRet>0
		IF RECCOUNT("crsCols")>0

			SCAN FOR NAME="MS_Description"
				N=ALINES(aRows, VALUE,1+4)
				lcComment=""
				FOR i=1 TO N
					lcComment = lcComment + "["+aRows[i]+"]"+IIF(i<N, [+hcr+;]+0h0d0a0909, "")
				ENDFOR
				lcFullFld=FORCEEXT(tcTbl, ALLTRIM(objname))
*-- TEXT BLOCK BEGIN
				TEXT TO lcRow NOSHOW TEXTMERGE
	SetComment([<<lcFullFld>>],<<lcComment>>)
				ENDTEXT
*-- TEXT BLOCK END
				lcRows = lcRows +lcRow+hCr
			ENDSCAN
			USE IN SELECT("crsCols")
		ENDIF
	ELSE
		AERROR(ee)
		SET STEP ON
	ENDIF
*-- TEXT BLOCK BEGIN

	IF NOT EMPTY(lcRows+lcTablecomment)
		TEXT TO lcproc NOSHOW TEXTMERGE

PROCEDURE do_<<tctbl>>()
<<lcTablecomment>>
<<lcrows>>

*-------------------

		ENDTEXT
*-- TEXT BLOCK END
	ENDIF

	RETURN lcProc


*******************************
PROCEDURE getTableDescr(tcTbl)
	LOCAL lnWA, c, nRet, lcDescr
	lcDescr=""
	lnWA=SELECT()
*-- text block begin
	TEXT to c noshow textmerge
		SELECT * FROM ::fn_listextendedproperty (N'Description',
			N'user', N'dbo', N'TABLE', N'<<tcTbl>>', NULL, NULL)
	ENDTEXT
*-- text block end
	nRet = SQLEXEC(h, c, "curTemp")
	IF nRet>0 AND RECCOUNT("curTemp")>0
		lcDescr=ALLTRIM(curtemp.VALUE)
	ENDIF
	USE IN SELECT("curTemp")
	SELECT (lnWA)
	RETURN lcDescr
ENDPROC

*******************************
PROCEDURE OwnProcs()

	lcStartMerge="TEXT to c noshow textmerge"
	lcEndMerge="ENDTEXT"
*-- text block begin AND DO NOT MERGE HERE (contains code which will merge when run)
	TEXT to c noshow
*---------------------------------------------------
PROCEDURE SetComment(tcFld, tcComment)
	lcTable=JUSTSTEM(tcFld)
	lcField=JUSTEXT(tcFld)
	tcComment=strtran(tcComment, ['], [''])

*-- text block begin
	<<lcStartMerge>>
IF NOT EXISTS  (SELECT *
         FROM ::fn_listextendedproperty (N'MS_Description', N'user', N'dbo', N'TABLE', N'<<lcTable>>', N'COLUMN', N'<<lcField>>'))
   EXEC [sp_addextendedproperty] @name         = N'MS_Description',
        @value        = N'<<tcComment>>',
        @level0type   = N'user',
        @level0name   = N'dbo',
        @level1type   = N'TABLE',
        @level1name   = N'<<lcTable>>',
        @level2type   = N'COLUMN',
        @level2name   = N'<<lcField>>'
ELSE
   EXEC [sp_updateextendedproperty] @name         = N'MS_Description',
        @value        = N'<<tcComment>>',
        @level0type   = N'user',
        @level0name   = N'dbo',
        @level1type   = N'TABLE',
        @level1name   = N'<<lcTable>>',
        @level2type   = N'COLUMN',
        @level2name   = N'<<lcField>>'

	<<lcEndMerge>>
*-- text block end
	nRet=SQLEXEC(h, c)
	IF nRet<=0
		AERROR(ee)
		SET STEP ON
	ENDIF


PROCEDURE setTableDescription(tcTable, tcDescr)
	LOCAL c

	tcDescr=STRTRAN(tcDescr, ['], [''])

*-- text block begin
	<<lcStartMerge>>
IF NOT EXISTS   (SELECT *
	FROM ::fn_listextendedproperty (N'MS_Description', N'user', N'dbo',N'TABLE', N'<<tcTable>>' , NULL, NULL))
	EXEC [sp_addextendedproperty] @name         = N'MS_Description',
		@value        = N'<<tcDescr>>',
		@level0type   = N'user',
		@level0name   = N'dbo',
		@level1type   = N'TABLE',
		@level1name   = N'<<tcTable>>'
else
	EXEC [sp_updateextendedproperty] @name         = N'MS_Description',
		@value        = N'<<tcDescr>>',
		@level0type   = N'user',
		@level0name   = N'dbo',
		@level1type   = N'TABLE',
		@level1name   = N'<<tcTable>>'

	<<lcEndMerge>>
*-- text block end
	nRet=SQLEXEC(h, c)
	IF nRet<=0
		AERROR(ee)
		SET STEP ON
	ENDIF


	ENDTEXT
*-- text block end

*...so I must use this seemingly stupid trick
* but so it is when language is metalanguage writing itself
	c=STRTRAN(c, "<<lcStartMerge>>", lcStartMerge)
	c=STRTRAN(c, "<<lcEndMerge>>", lcEndMerge)
* could have set delimiters to something... same effect.
	RETURN c