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.
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 = 0
BEGIN
--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