A while back Juan-José van der Linden created a script that would reverse-engineer a database into an Anchor model. He was kind enough to donate that script to the community, and it is available in our forum. Now there’s also a second effort which is available in the form of the script below. This is a work in progress and it will be updated with more features in the future. Perhaps we can merge the best features from JJ’s script into this one or the other way around.
Please note that the script will use column statistics in order to determine if knots should be created, so it may take a long time to run when no statistics are available. It will reuse existing statistics, so a second run of the script is much faster. It tries to determine ties based on primary keys and matching column names.
The following script can be used to generate knot loading code, based on the data stored in the descriptions of the knots in the model after running the script above.
The following script can be used to generate source to target mappings for use with the sisula ETL framework, based on the data stored in the descriptions of the attributes in the model after running the script above.
The script has now been updated with features from the script made by JJ. Some bugs have been squashed as well. If you want to see an example model created by the generator you can have a look at this one: http://roenbaeck.github.io/anchor/?id=ahNzfmFuY2hvcm1vZGVsZXItaHJkcg4LEgVNb2RlbBjSuJkBDA.
Hello,
Great scripts, I’m currently giving it a try.
The definition of the table #header could be updated to support the introduction of the column [Persisted Sample Percent] in the output of DBCC SHOW_STATISTICS with SQL Server 2016 (13.x) SP1 CU4:
create table #header (
[Name] varchar(555),
[Updated] datetime,
[Rows] bigint,
[Rows Sampled] bigint,
[Steps] int,
[Density] float,
[Average key length] float,
[String Index] varchar(3),
[Filter Expression] varchar(555),
[Unfiltered Rows] bigint,
[Persisted Sample Percent] tinyint
);
https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-show-statistics-transact-sql?view=sql-server-ver15
Thanks!
KR
Adrien