Technical Article

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