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 
When doing bulk data changes it may be beneficial to disable indexes prior to starting the operation. Fortunately, SQL's rich metadata makes this very easy to automate in a robust fashion.
2018-05-18 (first published: 2015-10-19)
4,786 reads
If you think about it carefully, you’ll realise, that index maintenance is necessary and that it makes sense, and that SQL Server is not doing this task automatically according to the book.
2011-08-18
5,492 reads
Far too often, people who are not trained database administrators somehow end up responsible for a database. They lack the proper training and knowledge to maintain their database, and problems begin to develop. Here’s a primer for all those involuntary DBAs who need a crash course in database maintenance best practices.
2008-08-13
6,696 reads
This script will change the owner of the stored procedure to dbo. It must be run in the same database as the stored procedures.
2001-10-28
1,577 reads
Ever wanted to know whether your triggers are enabled or not? The ObjectProperty command allows you to select information about the status of objects in your database, returning 1 for True and 0 for False. The example below determines whether a single trigger is disabled. SELECT ObjectProperty(object_id('Trigger_Name'), 'ExecIsTriggerDisabled') There are a number of other useful […]
2001-10-28
1,195 reads