I’ve been using ApexSQL I like their output, you can always get a trial its fully functional.
@Haso - Thanks for posting the 2021.1 release!
Is there anything much different with 2021.2? We’re about to start our upgrade testing next week, and hopefully upgrade in February (from 10.2.700). Thanks again!
1 Like
Love this tool, just going to say it again.
Any chance for a Kinetic 2022 chm or has it not changed enough from 2021? I’m using 2021 for now for Schema changes from 10.x
1 Like
May I know how can i see the relationship diagram in the kinetic 2021 chm?
Kinetic 2022.1 Schema .CHM
Download
Bonus
For those who cant use .chm files
https://www.dropbox.com/s/yudirhvy8rkdqy7/Kinetic%202022.1%20HTML%20Version.7z?dl=0
Reminder
Remember that on Windows 10 you must right click on the properties of the file and Unblock it and for Windows 7 you must uncheck the Ask Me security checkbox on the Run Dialog. Also don’t run it from an UNC Path - WinHelp Bugs - or you will get Blank Content.
Lastly, this is for educational purposes only. Use it as your sidekick to easily lookup column names and their purpose.
9 Likes
Thanks Haso! You rule.
Thanks!
Thanks
1 Like
Thanks Haso!
Yes I am in 2022.1 but not in 2021.1 oddly enough I just upgraded to .5 and the Data Dictionary shows correct. Odd. I’ll be building the 2022.2 soon anyways and I’ll keep an eye out - not sure what happened.
1 Like
Their field help in epicor does the same thing so it’s not you.
2 Likes
Thanks about Unblock it.
@hkeric.wci - Starting with 2023.1 we are adding a DB Upgrade step that will populate the Data Base Descriptions. The processing to do that will run sometime after the initial upgrade so we don’t slow down the upgrade process itself.
Thanks for the idea 
4 Likes
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
2 Likes
66K + columns (that’s just ERP Schema), yep that will take a bit of time. Yikes!
Download
Notes
I usually use the very basic chm because I usually want to search tables. There is a Stored Procedures chm that includes functions, sps, tables with more info among other things. But that makes the searching more noisy. Hence why I dont just generate that, its useless for a Business Analyst who is using this to quickly bring up column details. It’s just cool that’s all.
Reminder
Remember that on Windows 10 you must right click on the properties of the file and Unblock it and for Windows 7 you must uncheck the Ask Me security checkbox on the Run Dialog. Also don’t run it from an UNC Path - WinHelp Bugs - or you will get Blank Content.
Lastly, this is for educational purposes only. Use it as your sidekick to easily lookup column names and their purpose.
@Rich thanks for pinging me, I forgot to upload - here we go!
7 Likes
It takes me with 8GB of RAM about 20 minutes. To populate Tables and Columns. But if Epicor from the get-go populates it in their Database Seed, eventually it’ll just come pre-populated and viola.
2 Likes