Document SQL Server 2000/2005 Database

Download script - 3.26 KB

Introduction

Here is the script to generate the HTML document of your SQL Server 2000/2005 database. It is reverse engineering after the database is created. I hope it will be useful to many of you.

Background

While working as a part time DBA for some projects, I encountered difficulties in documenting and publishing my databases because I do not have any tool available to generate the document. I have written this script that gives the output in HTML that is easy to be modified/published/distributed.

Using the Code

Follow these steps to use this script:

Download the attached script file and open it in Management Studio (tested on version 9.00).
Select the database for which you want to generate document.
Select Result to File (Ctrl+Shift+F) from Query - Results To menu.
Execute (F5) the script.
In the Save Result dialogue box, type a file name (filename.htm/filename.html) in the File Name text box and select All Files (*.*) in Save As Type dropdown.
After successful execution of query, check the output file.

Note: For populating the description of table and columns, update the Description property of table and columns while creating database diagram or add extended property for them with name as MS_Description.

I hope this information will be helpful. I am planning to provide details of code in my next update.

Updates

Bug in getting index column is resolved
Bug in getting check constraints list is resolved
Definition of check constraints is added to document
Table Summary is changed to Table Description
Table and column description is populated from extended property

Download documentation application from here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Nitinpatel

Database Developer
Dillistone System

United Kingdom

Member

With 6 years of experience of working on Microsoft platform, currently I am working as Database expert.

My skill set includes
-SQL Server 2000/2005/2008
-.Net 2.0/3.5
-ASP/ASP.Net
-Winforms
-C#,VB.Net
-XML, HTML
-Microsoft CRM

Article Top

Poor

Excellent

Add a reason or comment to your vote: x
Votes of 3 or less require a comment

Comments and Discussions

You must Sign In to use this message board. (secure sign-in)

Search this forum
	Profile popups Noise Layout Per page

My vote of 5

formthis.com

9:07 10 May '12

Works perfectly with SQL Server 2008 R2!
Sign In·View Thread·Permalink

My vote of 5

G.Suman Babu

4:06 20 Apr '12

Good Article
Sign In·View Thread·Permalink

Very good Article

G.Suman Babu

4:05 20 Apr '12

Very good Article Thank,
Sign In·View Thread·Permalink

Excellent script

mickegus

20:35 9 Feb '12

Very nice script to document a database very fast. Thank you. Micke
Sign In·View Thread·Permalink

Great job!

PiotrZajac

8:08 5 Feb '12

Thx a lot! Regards Piotr
Sign In·View Thread·Permalink

My vote of 5

Cassnat

12:28 14 Dec '11

Very cool! thanks
Sign In·View Thread·Permalink

Flippin Cool!

StewStyle

23:06 12 Dec '11

Thanks, really neat and useful.
Sign In·View Thread·Permalink

My vote of 5

Bastiaans

0:17 1 Jun '11

Works very good! Thanks
Sign In·View Thread·Permalink

[My vote of 1] Thanks

Mamun Reza

18:44 30 May '11

This is very helpful and excellent script. Thanks man!!!
Sign In·View Thread·Permalink

Thanks!

bougie

3:45 4 May '11

Excellent script. Thanks!
Sign In·View Thread·Permalink

Thanks

Nayan S. Patel

15:53 27 Apr '11

Thanks for sharing this. Incase someone looking for free tool to document sql server database[^] Please check BIDocumenter from Pragmaticworks
Sign In·View Thread·Permalink

My vote of 5

kiran.puchakayala

18:18 22 Apr '11

Awesome work nitinpatel
Sign In·View Thread·Permalink

Thank you!!

Member 7665595

5:39 11 Feb '11

Thank you very much. You saved my day today. This is exactly what I am looking for.
Sign In·View Thread·Permalink

Thank you

samirMistry

20:39 18 Oct '10

Hello, I am web developer. And It help me a lot to understand our old database. Thank you so much.
Sign In·View Thread·Permalink

Thank you very much! .good job

koke17

0:40 1 Oct '10

Clean, simple to use... unbelieable !!!
Sign In·View Thread·Permalink

Very good work (PERFECT!!!)

Albric

23:47 2 Sep '10

Hello Thank you very much for your work. it is perfect!!!!!
Sign In·View Thread·Permalink

My vote of 5

aivanq01

5:31 2 Sep '10

Very helpful
Sign In·View Thread·Permalink

Awesome script

aivanq01

5:30 2 Sep '10

Thanks a lot for your effort, this an excelent time saver.
Sign In·View Thread·Permalink

My vote of 4

NArgade

23:53 22 Aug '10

This is a good efforts!!!
Sign In·View Thread·Permalink

Thank You

silverscouts

15:24 9 Apr '10

Great Work Thank You...
Sign In·View Thread·Permalink

Excelent time saver [modified]

adesilva@bigw.com.au

10:58 17 Feb '10

This is very good. Very useful. How about stored procedures ? Can we get out from this . It would me even better. Thanks Heaps modified on Saturday, February 27, 2010 4:34 PM
Sign In·View Thread·Permalink

Awesome! Just what I was looking for! Thanks!

miguelguzman

4:07 16 Feb '10

It worked perfectly! Nice job! Miguel Guzmán-Centeno Software Developer
Sign In·View Thread·Permalink

Version en Español con Procedimientos Almacenados para SQL Srv 2005

Ronald Fallas Rojas

9:49 17 Dec '09

Muchas gracias por el código q publicaste...
En esta nueva versión cambié palabras en inglés a español y con la ayuda del agregado de Store Procedures propuesto por el otro compañero, le agregué store procedures para SQL Srv 2005, además muestra el código de los store procedures

http://rapidshare.com/files/322251328/DB_Document_Version_1.2.sql[^]

Cool | :cool:



Truncate Table #StoredProcedures

IF @SqlVersion = '2000' 

	BEGIN

	INSERT INTO #StoredProcedures(object_id, name, type, [description], Code)

		SELECT so.id, '[dbo].[' + CAST(so.name AS varchar(155)) + ']', 'Procedimiento Almacenado', (SELECT CAST(sp.value AS varchar(4000)) FROM sysproperties sp WHERE sp.id = so.id) AS Comment, '' 

		FROM sysobjects so WHERE so.type ='P' AND so.Category <> 2

END

else if @SqlVersion = '2005' 

		begin

		insert into #StoredProcedures (Object_id, Name, Type, [description], Code)

		--FOR 2005

		Select o.object_id,  '[' + s.name + '].[' + o.name + ']', 

				'Procedimiento Almacenado',  

				cast(p.value as varchar(4000)),

				ISNULL(smsp.definition, ssmsp.definition)

				from sys.objects o 

					left outer join sys.schemas s on s.schema_id = o.schema_id 

					left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description' 

					LEFT OUTER JOIN sys.sql_modules AS smsp ON smsp.object_id = o.object_id

					LEFT OUTER JOIN sys.system_sql_modules AS ssmsp ON ssmsp.object_id = o.object_id

				where type = 'P' 

				order by type, s.name, o.name

end

 

SET @MaxK = (SELECT COUNT(id) FROM #StoredProcedures)

	SET @k = 1

	set @sr = 1

	

print '<table border="0" cellspacing="0" cellpadding="0" width="100%" id = "index2"><tr><td><b>Procedimientos Almacenados</b></td></tr></table>' 

		print '<table border="0" cellspacing="1" cellpadding="0" width="100%"><tr><th>Num</th><th>Nombre</th><th>Tipo</th></tr>' 

		set @Output = ''

		set @last = ''

		set @current = ''

	WHILE(@k <= @MaxK)

	BEGIN

		SELECT @Output =  '<tr><td align="center">' + Cast((@k) as varchar) + '</td><td><a href="#' + Type + ':' + name + '">' + name + '</a></td><td>' + Type + '</td></tr>' 

				from #StoredProcedures where id = @k	

		PRINT @Output

		SET @k = @k + 1

	END

print '</table><br />'

Truncate Table #SpColumns

SET @k = 1

WHILE(@k <= @maxk)

BEGIN

	--table header

	SELECT @Output =  '<tr><th align="left"><a name="' + Type + ':' + name + '"></a><b>' + Type + ':' + name + '</b></th></tr>',  @description = [description]

			FROM #StoredProcedures WHERE id = @k

	

	PRINT '<br /><br /><br /><table border="0" cellspacing="0" cellpadding="0" width="100%"><tr><td align="right"><a href="#index2">Indice</a></td></tr>'

	PRINT @Output

	PRINT '</table><br />'

	PRINT '<table border="0" cellspacing="0" cellpadding="0" width="100%"><tr><td><b>Descripcion</b></td></tr><tr><td>' + isnull(@description, '') + '</td></tr></table><br />' 

 

	--table SpColumns

 

		-- look in the syscoloumns table to get the coloumn (input, output parameters) names

		INSERT INTO #SpColumns  (Name, Type, Output)

		--FOR 2000

			SELECT sc.name, st.name + (

				CASE WHEN (st.name = 'varchar' or st.name = 'nvarchar' or st.name ='char' or st.name ='nchar')

					THEN '(' + cast(sc.length as varchar) + ')' 

				WHEN st.name = 'decimal'  

					THEN '(' + cast(sc.prec as varchar) + ',' + cast(sc.scale as varchar)   + ')' 

				ELSE ''

			END	)		

			, isOutparam  

			FROM syscolumns sc, master..systypes st, sysobjects so 

			WHERE so.id = (SELECT SP.object_id FROM #StoredProcedures SP

				WHERE SP.ID = @k)

			AND  sc.id = so.id 

			AND sc.xtype = st.xtype 

 

	SET @maxj =   @@rowcount

	SET @j = 1

 

	PRINT '<table border="0" cellspacing="0" cellpadding="0" width="100%"><tr><td><b>Columnas</b></td></tr></table>' 

	PRINT '<table border="0" cellspacing="1" cellpadding="0" width="100%"><tr><th>Num</th><th>Nombre</th><th>Tipo</th><th>Col. Salida</th></tr>' 

	

	WHILE(@j <= @maxj)

	BEGIN

		SELECT @Output = '<tr><td width="20px" align="center">' + Cast((@j) as varchar) + '</td><td width="150px">' + isnull(name,'')  + '</td><td width="150px">' +  upper(isnull(Type,'')) + '</td><td width="50px" align="center">' + isnull(Output,'0') + '</td></tr>' 

			FROM #SpColumns  WHERE id = @j

		

		PRINT 	@Output 	

		SET @j = @j + 1;

	END

	

	-- Obtain sp code

 

	declare @LargoCodigoSP as int

	set @LargoCodigoSP = (select LEN(Code) FROM #StoredProcedures WHERE id = @k)

	declare @InicioBloqueSp as int, @LargoBloqueSP as int

	declare @Out1 as varchar(max)

	set @InicioBloqueSP = 1

	if (@LargoCodigoSp > 7900)

	Begin

		set @LargoBloqueSP = 7900

	End

	Else

	Begin

		set @LargoBloqueSP = @LargoCodigoSP

	End

	PRINT '<tr><td align="left" colspan="4"><div class="code-comment">'

	while (@InicioBloqueSP < @LargoCodigoSP)

	Begin

		set @Out1 = (select substring(Code,@InicioBloqueSP,@LargoBloqueSP) FROM #StoredProcedures WHERE id = @k)

		--reemplazar los cambios de linea

		set @Out1 = (Select REPLACE(@Out1,'

', '<br />'))

		print @Out1

		set @InicioBloqueSP = @InicioBloqueSP + @LargoBloqueSP

		set @LargoBloqueSP = 7900

		if (@LargoBloqueSP > (@LargoCodigoSP - @InicioBloqueSP))

		Begin

			set @LargoBloqueSP = @LargoCodigoSP - @InicioBloqueSP + 1

		End

	End

	PRINT '</div></td></tr>'

 

	PRINT '</table><br />'

    SET @k = @k + 1;

END

Ronald Fallas

Re: Version en Español con Procedimientos Almacenados para SQL Srv 2005

Ronald Fallas Rojas

10:32 17 Dec '09

Hay una pulguita con el truncate de #SpColumns, hay q ponerlo dentro del While `Truncate Table #SpColumns INSERT INTO #SpColumns (Name, Type, Output)` Ronald Fallas
Sign In·View Thread·Permalink	5.00/5 (1 vote)

Add code for table-valued and scalar functions, and stored procedures?

TechVsLife

17:06 21 Nov '09

Very useful, it would be perfect if could add sql 2005/2008 code for table-valued & scalar functions, and stored procedures. (Also could be turned into a stored procedure with parameter of output html file, and load or print option.)
Sign In·View Thread·Permalink	2.50/5 (2 votes)