A previous article discussed
a model generation scheme
utilizing SoProMach
that allows model architects to
effectively acquire knowledge and gain control of existing
Oracle databases. The very same approach can be used with SQL Server
databases, as described in this article. For a full overview and rationale
of the suggested approach (CSV vs. XML, XML and Somusar EF files, and so forth)
refer to the
article on Oracle metadata mining.
The specific solution described in this article allows to extract
metadata from Microsoft SQL Server 2005 Express Edition - Community Technology Preview December 2004. The extracted metadata describe the well-known
sample databases pubs and Northwind as well as the more recent and
feature-rich AdventureWorks database that features SQL schemas.
By means of SoProMach and SQL Server's sqlcmd this solution
extracts metadata about the following classes of constructs:
tables and constraints;
views;
stored procedures and functions;
The overall process of this specific solution is shown in the figure below.
The numbers in the figure correspond to the steps described
below. Steps 1 to 7 (continuous lines) are project-independent;
steps 8-10 (dashed lines) are project-specific.
The Model Architect runs the desired metadata extraction job on the desired database. Note that the Model Architect could be an automated tool;
SoProMach generates on the fly a set of scripts, similar to the queries that a SQL Server DBA would type into a SQL Server's sqlcmd console;
SoProMach submits each script to SQL Server's sqlcmd;
SQL Server's sqlcmd interacts with the RDBMS and retrieves the metadata;
The raw metadata is written by SQL Server's sqlcmd into a set of text files;
SoProMach parses the raw metadata; note that steps 2 to 6 are automatically repeated to fetch metadata about tables, views, stored procedures, and functions;
The interesting part of the metadata is rearranged and stored into CSV files;
CSV files can be examined and analyzed by the Model Architect as plain spreadsheets, or processed by record-oriented tools;
Depending on the project target, the Model Architect runs SoProMach to generate new software, or to produce a new model (XML, or .ef, for instance) for later processing;
According to the target of step #9, SoProMach transforms the uniform metadata into more complete metadata, or directly into software.
This solution has been tested with Microsoft SQL Server 2005 Express Edition - Community Technology Preview December 2004 and
SQL Server's sqlcmd running on both Windows 2000. It has been implemented
with SoProMach/Community Edition.
