Introduction Use embedded file annotations when programming is a convenient and effective way, because such annotations can be refreshed in memory. Many development languages, such as Java and C # support this feature. Unfortunately, SQL Server does not support.
TSQLDOC is a Windows script interpreter that extracts related metadata and embedded annotations from the storage process of Transact-SQL. This article describes the corresponding XML annotation files for the stored procedures for the database, and then converts the XML annotation file into HTML or other format files with XSLT. The following is described below, and the example source program can be downloaded in the art attachment.
Use TSQLDOCTSQLDoc to support a single line of file comments:
File Note Content #Desc Stored Procedure Description #author author #Version version #param parameter description
After adding appropriate annotations during storage, TSQLDoc can take out annotation text and data types and parameters used in SQL-DMO (Note 1). Then list the annotation text in the Transact-SQL code by VBScript rule expression. Tsqldoc will generate the corresponding XML annotation file according to TSQLDoc.dtd (document download).
Script Run Here you use scripts and SQL Server's Pubs database. First download the TSQLDoc.vbs file at the end and confirm that your machine meets the system requirements (Note 2). Then run the following script in the command line: cscript tsqldoc.vbs -user [Your username] -pwd [Your Password] -db pubs -out pubs.xml
TSQLDoc will connect to the local server and remove the corresponding data (Pubs.xml) from the PUBS database. This XML file generates a
Now add a stored procedure to the database, add a comment (Note 3):
Create Procedure Docexample (@ param1 integer, @ param2 varchar (50), @ param3 datetime, @outparam integer output) AS
- #author morten witTrock - #Version 1.0 - #Desc created with the purpose of demonstrating tsqldoc Documentation Comments
- #param @ param1 contains an integer - #param @ param2 contains a string - #param @ param3 contains a date - #param @outparam integer output parameter - This example is not actual operation code
Run the above script, get the XML file as follows:
Generating HTML text Tsqldoc can convert an XML annotation file into an HTML page (or other format file) with XSLT. Add the appropriate XSLT file name in the script running and specify the output to html file: cscript tsqldoc.vbs -user [Your username] -PWD [Your Password] -db pubs -xslt transform.xsl -out pubs.html
Conclusion Tsqldoc is a basic tool for text annotations for SQL Server. I hope everyone will modify and develop its use. My suggestion is as follows:
Increase support for database objects, not just stored procedures and function comments. Add metadata comments on tables, columns, and views. Increase annotations for user functions. A corresponding report is made for database objects that have been commented.
If you find a problem in my program or have other suggestions, please give me a letter.
Have fun!
-------------------------------------------------- ------------------
Note 1: SQL-DMO (SQL Distributed Management Objects) is an object set that enables SQL Server developers to process and manage databases. Details can be referred to the attachment link.
Note 2: Run TSQLDoc, system requirements: Microsoft XML Core Services 4.0 VBScript Version 5.0 or Later SQL-DMO (automatically installed when installing SQL Server).
Note 3: The instpubs.sql script under the root directory of the SQL Server can restore the PUBS database.
Http://www.sqlservercentral.com/columnists/mwittrock/tsqldoc.vbs
http://www.sqlsercentral.com/columnists/mwittrock/tsqldoc.dtd
Http://www.sqlservercentral.com/columnists/mwittrock/transform.xsl
http://www.sqlservercentral.com/articles/articlelist.asp?categoryID=53
