Documenting Database Code

Question

Post reply

Documenting Database Code

jvanderberg

SSCertifiable

Points: 6762
More actions
September 16, 2010 at 7:34 am

#97586

Was wondering if anyone had any recommended programs or scripts for documenting database code. Specifically, I'd like the documentation to be in HTML format. I'd also really like something that will tell me what the result set for a stored procedure or table-valued function will be (if this is even possible).

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

crazy4sql SSCoach Points: 19590 More actions · Answer 1

crazy4sql

SSCoach

Points: 19590

September 16, 2010 at 7:55 am

#1222202

sqldoc is one of the tool from redgate.

----------
Ashish

Lowell SSC Guru Points: 323482 More actions · Answer 2

j take a look at this thread from a week or so ago; there is a script contribution that I enhanced and fixed slightly;

http://qa.sqlservercentral.com/Forums/Topic981671-149-1.aspx#bm981831

it produces well formatted, informative html document.

I've taken that script and enhanced it to my own needs, but it's an awesome documentation starting point. I've got an even more enhanced version that also includes the table CREATE TABLE DDL definitions and the proc/view/function scripts in it as well.

db_documentation_enhanced.txt

here's a link to the results after pointing it against my master database(which has a lot of user objects, but that's another issue entirely)

master_db_documentation.html[/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

nancy.lytle Default port Points: 1430 More actions · Answer 3

I ran the script and it does what you said it would. Very nice. I have used the BIDocumentor tool because it goes down to the column level, you can click a column name and find every object where it is used.

This kind of documentation or anykind of documentation is what most shops need and don't have available.

Thanks again for the script.

Nancy

Lowell SSC Guru Points: 323482 More actions · Answer 4

J i poked around, and did nto find a way to get the output of the columns/structure of a stored procedure (if it returns result set) so far;

if we can find a way to do that, i could help adding that section to that script example...assuming it's even close to what you are after.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jvanderberg SSCertifiable Points: 6762 More actions · Answer 5

I found a way, but I'm having trouble getting it to work.



DECLARE @nvQueryRemote nvarchar(MAX)

DECLARE @nvQueryLocal nvarchar(MAX)

SET @nvQueryRemote = N'SET FMTONLY ON; EXEC iERP81GA_LIVE.vmfg.uspGetAllLocSalesOrder'

SET @nvQueryLocal = N'SELECT * FROM OPENROWSET(''SQLOLEDB'', ''SERVER=(local);Trusted_Connection=Yes'',

  ''' + @nvQueryRemote + N''')'

PRINT @nvQueryLocal

EXEC (@nvQueryLocal)

This is just the framework, I'd actually want to SELECT * INTO <temp table> FROM OPENROWSET, but I keep getting an error:

Msg 7357, Level 16, State 1, Line 1

Cannot process the object "SET FMTONLY ON; EXEC iERP81GA_LIVE.vmfg.uspGetAllLocSalesOrder". The OLE DB provider "SQLNCLI" for linked server "(null)" indicates that either the object has no columns or the current user does not have permissions on that object.

Note that running just the command "SET FMTONLY ON; EXEC iERP81GA_LIVE.vmfg.uspGetAllLocSalesOrder" works fine.

Lowell SSC Guru Points: 323482 More actions · Answer 6

my snippets have an open query example slightly different;

i know i've used this to insert into a local temp table



SELECT *

  INTO #tmp

   FROM OPENROWSET('SQLOLEDB','Server=yourservernamehere;Trusted_Connection=Yes;Database=Master',

                              'Set FmtOnly OFF; EXEC dbo.sp_Who')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

jvanderberg SSCertifiable Points: 6762 More actions · Answer 7

jvanderberg

SSCertifiable

Points: 6762

September 16, 2010 at 9:20 am

#1222284

Alright, I guess that's not working for stored procedures that run against another linked server. So I guess that solution won't work. Wonder how BIDS does it... maybe I'll profile it and find out.

jvanderberg SSCertifiable Points: 6762 More actions · Answer 8

All I'm getting from the profiler is that it's actually executing the stored procedure. Apparently BIDS is getting the field names by executing the script programmatically, and there doesn't appear to be any way to get this information from T-SQL.

EDIT: The method above does work for some scripts, but I'm thinking anything with an EXEC or something similar will not work.