User tables documentation - HTML format

,

It creates documentation for all user tables in a specific database.

A server name/ database name was added on the top of the HTML file also a column datatype field was added to the HTML file.

Now the @strHTML can have 8000 chars, before only 4000 - still for tables with a lot of columns the size of 8000 will not be enough, in that case @strHTML will be truncated. - thanks to Brian Nordberg for catching it.

-- Created by Ionel Catanoiu
-- Created on: Oct. 27, 2003
-- Updated on: Oct. 29, 2003

DECLARE @table_id int, @strHTML varchar(8000), @strHTML_header varchar(500)

--initialize HTML header string
SET @strHTML_header = 'Server Name: <b>'+ @@servername + '</b><br> Database Name <b>: ' + DB_NAME() + '</b><br><br>'
				 
DECLARE cursor_documentation CURSOR FOR
	SELECT DISTINCT id 
	FROM sysobjects 
	WHERE OBJECTPROPERTY(sysobjects.id, 'IsMSShipped') = 0 AND
		sysobjects.type = 'U'


OPEN cursor_documentation

FETCH NEXT FROM cursor_documentation INTO @table_id

WHILE (@@FETCH_STATUS = 0)
	BEGIN
		--building HTML tables documentation
		SELECT @strHTML = @strHTML_header + 'Table: <b>' + sysobjects.name + '</b><br><br> <table border ="1" 
			cellpadding ="2" cellspacing ="0" border = "1" bordercolor = "003366" 
			width = "690">
				<tr bgcolor = "eeeeee">
				  <td align = "center" width = "100"><B>table name</td>
				  <td align = "center" width = "100"><B>column name</td>
				  <td align = "center" width = "50"><B>data type</td>
				  <td align = "center" width = "10"><B>length</td>
				  <td align = "center" width = "10"><B>precision</td>
				  <td align = "center" width = "20"><B>scale</td>
				  <td align = "center" width = "200"><B>collation</td>
				  <td align = "center" width = "200"><B>comments</td>
				</tr>' 
		FROM sysobjects
		WHERE sysobjects.id = @table_id

		--PRINT len(@strHTML)

		SELECT @strHTML = @strHTML + '<tr><td>' + convert(varchar(200), sysobjects.name) + '</td> <td>' + 
			convert(varchar(200), syscolumns.[name]) + '</td> <td>' + 
			convert(varchar(50), systypes.[name]) + '</td> <td>' + 
			ISNULL(convert(varchar(5), syscolumns.length), ' ') + '</td> <td>' + 
			ISNULL(convert(varchar(5), syscolumns.prec), ' ') + '</td><td>' + 
			ISNULL(convert(varchar(5), syscolumns.scale), ' ') + '</td><td>' + 
                      	ISNULL(convert(varchar(100), syscolumns.[collation]), ' ') + '</td><td>' + 
			ISNULL(convert(varchar(500), sysproperties.[value]), ' ') + '</td></tr>'
		FROM sysobjects INNER JOIN
              		syscolumns ON sysobjects.id = syscolumns.id INNER JOIN
              		systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
              		sysproperties ON syscolumns.colid = sysproperties.smallid AND syscolumns.id = sysproperties.id
		WHERE sysobjects.id = @table_id

		

   		SELECT @strHTML = @strHTML + '</TABLE><br><br>'

		SET @strHTML_header = ''

		--PRINT len(@strHTML)

		PRINT @strHTML
				
	
		FETCH NEXT FROM cursor_documentation INTO @table_id
	END

CLOSE cursor_documentation
DEALLOCATE cursor_documentation

Rate

Share

Share

Rate