We are using Red Gates SQLDoc (http://www.red-gate.com) tool to document our databases and it works just fine. But as a matter of fact we do our documentations on the test server not the real server. And then a problem arises.
SQLDoc has no tool to copy the documentation from one DB to another.
And that’s the problem because SQLDoc (and other tools including SQL Management Console itself) put the documentation text into the extended properties of SQL Server 2000 and SQL Server 2005, so you cannot simply copy the documented tables, procedures or functions within the help file. And documenting on the master DB did not prove usefull for us.
So I checked the internals of the extended properties on SQL Server and found out that it uses the procedures sp_addextendedproperty and sp_dropextendedproperty and the internal, undocumented system table SYSPROPERTIES to save the documentation text onto the database. The SQL-Script below will extract your documentation and produce the needed procedure calls in the result window. Simply copy the text from the result window and execute it on the other database. The other database has to have the same schema of course! And because the existence of an extended property is not checked before you will receive some errors when executing the sp_drop… procedures, but this has no effect on the db.
Here’s the script:
DECLARE @name NVARCHAR(4000), @value NVARCHAR(4000), @level0type NVARCHAR(4000), @level0name NVARCHAR(4000), @level1type NVARCHAR(4000), @level1name NVARCHAR(4000), @level2type NVARCHAR(4000), @level2name NVARCHAR(4000) DECLARE @statement NVARCHAR(4000)DECLARE cloop CURSOR LOCAL for SELECT S.NAME AS NAME, REPLACE(CAST(S.VALUE AS NVARCHAR(4000)), ””, ”””) AS VALUE, ‘USER’ AS LEVEL0TYPE, ‘dbo’ AS LEVEL0NAME, CASE O.XTYPE WHEN ‘U’ THEN ‘TABLE’ WHEN ‘P’ THEN ‘PROCEDURE’ WHEN ‘FN’ THEN ‘FUNCTION’ WHEN ‘TF’ THEN ‘FUNCTION’ WHEN ‘IF’ THEN ‘FUNCTION’ WHEN ‘V’ THEN ‘VIEW’ END AS LEVEL1TYPE, O.NAME AS LEVEL1NAME, CASE S.TYPE WHEN 4 THEN ‘COLUMN’ ELSE ‘NULL’ END AS LEVEL2TYPE, CASE WHEN ISNULL(C.NAME, ”) = ” THEN ‘NULL’ ELSE C.NAME END AS LEVEL2NAME FROM SYSPROPERTIES S JOIN SYSOBJECTS O ON S.ID = O.ID LEFT JOIN SYSCOLUMNS C ON S.ID = C.ID AND S.SMALLID = C.COLORDER –LEFT JOIN SYSINDEXES I ON S.ID = I.ID AND S.SMALLID = I.INDID WHERE S.NAME = ‘MS_Description’ ORDER BY 5,6,7,8 OPEN cloop
FETCH next FROM cloop INTO @name, @value, @level0type, @level0name, @level1type,
@level1name, @level2type, @level2name WHILE @@FETCH_STATUS = 0BEGIN –drop property SET @statement = ‘exec sp_dropextendedproperty N”’ + @name + ”’, ”’ + @level0type + ”’, ”’ + @level0name + ”’, ”’ + @level1type + ”’, ”’ + @level1name + ”’, ”’ + @level2type + ”’, ”’ + @level2name + ”” SET @statement = REPLACE( @statement, ”’NULL”’, ‘NULL’) PRINT ‘/*’ + @level1type + ‘: ‘ + @level1name + ‘*/’ PRINT @statement PRINT ‘GO’ –add property SET @statement = ‘exec sp_addextendedproperty N”’ + @name + ”’, N”’ + @value + ”’, ”’ + @level0type + ”’, ”’ + @level0name + ”’, ”’ + @level1type + ”’, ”’ + @level1name + ”’, ”’ + @level2type + ”’, ”’ + @level2name + ”” SET @statement = REPLACE( @statement, ”’NULL”’, ‘NULL’) –PRINT ‘/*’ + @level1type + ‘: ‘ + @level1name + ‘*/’ PRINT @statement PRINT ‘GO’
FETCH next FROM cloop INTO @name, @value, @level0type, @level0name,
@level1type, @level1name, @level2type, @level2name END CLOSE cloop DEALLOCATE cloop