Page 1 of 3 123 LastLast
Results 1 to 15 of 41
  1. #1
    Join Date
    Jan 2008
    Posts
    186

    Unanswered: Documenting Sql server stored procedures

    Can anyone give me an example of how they typically document their stored procedure.

    By default, the management studio provides the following for me:
    Code:
    -- =============================================
    -- Author:	
    -- Create date: 
    -- Description:	
    -- =============================================
    CREATE PROCEDURE myProc ...
    What do you guys typically do?

  2. #2
    Join Date
    Sep 2005
    Posts
    161
    Comments inside the SP are good. This might be an overkill, but I have a small application that I use to document my SP's. It contains one table with some columns including a foreign key to the sysobjects ID. I run a report that generates printed documentation using my table, sysobjects, and syscomments.

  3. #3
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I use SqlSpec, but I'm biased since I wrote it.

  4. #4
    Join Date
    Sep 2005
    Posts
    161
    I've noticed a lot of people here have authored tools. I feel like I need to author something to fit in

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by jezemine
    I use SqlSpec, but I'm biased since I wrote it.
    No offense, but it's vvvvv-eeeee-rrrrr-y sssssssssssslllllllllllllllllllooooooooooooooow... I started it 10 minutes ago against a db with 300 tables, and it's just half way through...saying that it's "Fetching DDL..." How are you fetching it?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I take it all back! Slow or not, - it did a pretty decent job! Kudos for the java GUI by the way! And, a little secret, - it did run against Katmai 64-bit on Vista 64-bit! Not bad at all!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Quote Originally Posted by rdjabarov
    I take it all back! Slow or not, - it did a pretty decent job! Kudos for the java GUI by the way! And, a little secret, - it did run against Katmai 64-bit on Vista 64-bit! Not bad at all!
    thanks. I don't support 2008 officially yet so it's nice to know it worked.

    not sure what you mean about the java GUI - do you mean the javascript tree control? that's not mine - I licensed it from here: http://www.treeview.net It's the only tree control I found that worked reasonably well when you have 10k items in the tree (which is not uncommon - ever worked with SAP or GreatPlains? yuck!)

    about being slow: I'm using SMO to fetch table DDL, and SMO is not the most efficient of APIs. someday maybe I'll fix that bit to not use SMO, but it's non-trivial do it correctly across different server versions. Basically I am leveraging the MS SQL Server test team by using SMO.
    Last edited by jezemine; 06-19-08 at 20:19.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Make sure you have a section in your header to record the revision history:

    --Revisions-----------------------------------------------
    --blindman, 6/18/2008: Added pointless obfuscation
    ------------------------------------------------------------
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pointless?

    i don't think so -- not you

    your obfuscations always have a good reason

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    how about this one then:

    --Revisions-----------------------------------------------
    --blindman, 6/18/2008: Added this revision comment
    ------------------------------------------------------------

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I use SQL Spec for many things, and it does a wonderful job.

    I put a "one liner" description at the top of every procedure/view/function/script file/etc. that shows the my initials, the date in YYYY-MM-DD format (to make sorting work better), and a 20-70 character description.

    I document interesting lines of code with inline comments, but those tend to be rather sparse.

    I put volumes of information in the SCCS (like Source Safe, PVCS, RCS, etc). I write small novels describing what I did, why I did it that way, and every "gotcha" that occurs to me. I feel that these comments really belong in your Source Code Control System, not in your SQL scripts.

    -PatP

  12. #12
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by blindman
    Make sure you have a section in your header to record the revision history:

    --Revisions-----------------------------------------------
    --blindman, 6/18/2008: Added pointless obfuscation
    ------------------------------------------------------------
    Would you say this is necessary, even if I have source control in place?

  13. #13
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    it's not necessary. comments are never necessary since they are ignored by the compiler.

    it's useful though.

    at a glance you can see the history, so you know what dates to look at in your SC system if you want to do some diffs to see when this or that change went in.

  14. #14
    Join Date
    Jan 2008
    Posts
    186
    Quote Originally Posted by Pat Phelan
    I put a "one liner" description at the top of every procedure/view/function/script file/etc. that shows the my initials, the date in YYYY-MM-DD format (to make sorting work better), and a 20-70 character description.
    Do you have a sample of this, if you don't mind?

    I'm just trying to find a nice standard way of providing header comments for my sprocs... Like, do you detail them with fancy borders or anything like that?

    What about parameters? Do you document these in the header comments?

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Strongly disagree with Pat and Jez.

    Comments belong with the code, not the source control. Its very likely that people looking at your code in the future won't have access to your source control history.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •