Nice! That would help everyone. Generating the documentation is easy, once MS_Description data is filled in.
Not sure if these scripts are better than yours but feel free to use them. I had some SQL Genius a few years ago write these for me. Basically extracting the details from ZData and putting into MS_Description.Then I use any 3rd party tool to generate chm or html.
CREATE PROCEDURE [dbo].[AddExtendedPropertiesToColumns]
AS
BEGIN
SET NOCOUNT ON
DECLARE @SystemCode NVARCHAR(8) ,
@DataTableID NVARCHAR(128) ,
@FieldName NVARCHAR(128) ,
@Description NVARCHAR(4000) ,
@Progress NVARCHAR(MAX) ,
@Percent INT = 0 ,
@Total INT = 0 ,
@Current INT = 1
--get total rows count from table
SELECT @Total = COUNT(*)
FROM [Ice].[ZDataField]
--declare cursor for iterating through rows
--try to get [DBTableName] and [DBFieldName] first and if they are empty get [DataTableID] and [FieldName] instead
DECLARE Descriptions CURSOR FAST_FORWARD
FOR
SELECT [SystemCode] ,
CASE WHEN ISNULL([DBTableName], '') = ''
THEN [DataTableID]
ELSE [DBTableName]
END ,
CASE WHEN ISNULL([DBFieldName], '') = '' THEN [FieldName]
ELSE [DBFieldName]
END ,
[Description]
FROM [Ice].[ZDataField]
--open cursor
OPEN Descriptions
--fetch first row from cursor into variables
FETCH NEXT FROM Descriptions INTO @SystemCode, @DataTableID,
@FieldName, @Description
--while there are rows loop through them
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(select * from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
join sys.columns c on t.object_id = c.object_id
where t.name = @DataTableID and s.name = @SystemCode and c.name = @FieldName)
BEGIN
--if extended property exists update it, otherwise add it
IF EXISTS ( SELECT NULL
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@SystemCode
+ '.'
+ @DataTableID)
AND [name] = N'MS_Description'
AND [minor_id] = ( SELECT
[column_id]
FROM
SYS.COLUMNS
WHERE
[name] = @FieldName
AND [object_id] = OBJECT_ID(@SystemCode
+ '.'
+ @DataTableID)
) )
BEGIN
--update extended property
EXECUTE sp_updateextendedproperty @name = N'MS_Description',
@value = @Description,
@level0type = N'SCHEMA',
@level0name = @SystemCode,
@level1type = N'TABLE',
@level1name = @DataTableID,
@level2type = N'COLUMN',
@level2name = @FieldName;
END
ELSE
BEGIN
--add extended property
EXECUTE sp_addextendedproperty @name = N'MS_Description',
@value = @Description,
@level0type = N'SCHEMA',
@level0name = @SystemCode,
@level1type = N'TABLE',
@level1name = @DataTableID,
@level2type = N'COLUMN',
@level2name = @FieldName;
END
END
--set progress string
SET @Progress = 'Updated property for: ' + @SystemCode + '.'
+ @DataTableID + '.' + @FieldName
+ '; Current progress is '
+ CAST(@Current * 100 / @Total AS NVARCHAR(MAX)) + '%%'
--notify in message tab
RAISERROR(@Progress, 0, 1) WITH NOWAIT
--fetch next row from cursor
FETCH NEXT FROM Descriptions INTO @SystemCode, @DataTableID,
@FieldName, @Description
--increment current updated rows count
SET @Current = @Current + 1
END
--close cursor and free resources
CLOSE Descriptions
DEALLOCATE Descriptions
END
CREATE PROCEDURE [dbo].[AddExtendedPropertiesToTables]
AS
BEGIN
SET NOCOUNT ON
DECLARE @SystemCode NVARCHAR(8) ,
@DataTableID NVARCHAR(128) ,
@Description NVARCHAR(4000) ,
@Progress NVARCHAR(MAX) ,
@Percent INT = 0 ,
@Total INT = 0 ,
@Current INT = 1
--get total rows count from table
SELECT @Total = COUNT(*)
FROM [Ice].[ZDataTable]
--declare cursor for iterating through rows
--try to get [DBTableName] first and if it is empty get [DataTableID] instead
DECLARE Descriptions CURSOR FAST_FORWARD
FOR
SELECT [SystemCode] ,
CASE WHEN ISNULL([DBTableName], '') = ''
THEN [DataTableID]
ELSE [DBTableName]
END ,
[Description]
FROM [Ice].[ZDataTable]
--open cursor
OPEN Descriptions
--fetch first row from cursor into variables
FETCH NEXT FROM Descriptions INTO @SystemCode, @DataTableID,
@Description
--while there are rows loop through them
WHILE @@FETCH_STATUS = 0
BEGIN
IF EXISTS(select * from sys.tables t
join sys.schemas s on t.schema_id = s.schema_id
where t.name = @DataTableID and s.name = @SystemCode)
BEGIN
--if extended property exists update it, otherwise add it
IF EXISTS ( SELECT NULL
FROM SYS.EXTENDED_PROPERTIES
WHERE [major_id] = OBJECT_ID(@SystemCode
+ '.'
+ @DataTableID)
AND [name] = N'MS_Description'
AND [minor_id] = 0 )
BEGIN
--update extended property
EXECUTE sp_updateextendedproperty @name = N'MS_Description',
@value = @Description,
@level0type = N'SCHEMA',
@level0name = @SystemCode,
@level1type = N'TABLE',
@level1name = @DataTableID;
END
ELSE
BEGIN
--add extended property
EXECUTE sp_addextendedproperty @name = N'MS_Description',
@value = @Description,
@level0type = N'SCHEMA',
@level0name = @SystemCode,
@level1type = N'TABLE',
@level1name = @DataTableID;
END
END
--set progress string
SET @Progress = 'Updated property for: ' + @SystemCode + '.'
+ @DataTableID + '; Current progress is '
+ CAST(( @Current * 100 ) / @Total AS NVARCHAR(MAX))
+ '%%'
--notify in message tab
RAISERROR(@Progress, 0, 1) WITH NOWAIT
--increment current updated rows count
FETCH NEXT FROM Descriptions INTO @SystemCode, @DataTableID,
@Description
SET @Current = @Current + 1
END
--close cursor and free resources
CLOSE Descriptions
DEALLOCATE Descriptions
END