Biml Reverse Engineer a database

Biml Reverse Engineer a database, a.k.a. Biml to the rescue

I'm at a new client and I needed an offline version of their operation data store, ODS, database schema. I don't know what I was expecting, but it wasn't 11,500 tables. :O That's a lot. First up to bat was Visual Studio Database Projects. I clicked Import and you really have no options to winnow the list of items your importing down via Import. Ten minutes later, the import timed out on spatial indexes. Which wouldn't be so bad except it's an all or nothing operation with import.

Fair enough, I'll use the Schema Comparison and only compare tables, that should make it less ugly. And I suppose it did but still, the operation timed out. Now what?

SSMS to the rescue. I right click on my database and select Generate Scripts and first off, I script everything but the tables. Which is amusing when you have 11.5k tables, checking and unchecking the table box causes it to spin for a bit. I generated a file for each object with the hope that if the operation goes belly up, I'll at least have some progress. Round 1, Winner! I had all my views, procedures, functions, data types (don't hate), all scripted out nice and neat. Round 2, I just selected tables. And failed.

Maybe I didn't want all the tables. They have the ODS broken out by schemas to identify the data source and I only wanted the CMS data for this first draft. I run back through the Generate Scripts wizard this time only selecting tables in the CMS schema. That significantly reduced the number of objects I needed to script but still, it failed. And my mouse finger was tired. There had to be a better way.

Of late, Biml seems to be that better way. In just a few lines, I created a connection to my database, reverse engineered the targeted schema and then wrote the SQL out to files (so I could then import them with a database project). How cool is that?

inc_Connections.biml

I first added a biml file to my SSIS project that contained an OLE DB Connection Manager to the database I was interested in.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Connections>
        <OleDbConnection Name="ODS" ConnectionString="Data Source=localhost\DEV2014;Initial Catalog=ODS;Provider=SQLNCLI11.0;Integrated Security=SSPI;"/>
    </Connections>
</Biml>

ExportTables.biml

Here's the "magic". There are three neat tricks in here.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#@ template tier="1" #>
<#@ import namespace="Varigence.Biml.CoreLowerer.SchemaManagement" #>
<#@ import namespace="System.IO" #>
<#
    var schema = new List<string>{"CMS"};
    var ODSCM = RootNode.OleDbConnections["ODS"];
    var ODSDB = ODSCM.GetDatabaseSchema(schema, null, ImportOptions.None);
    string fileNameTemplate = @"C:\Users\fellowsb\Documents\ODSDB\{0}_{1}.sql";
    string currentFileName = string.Empty;
    foreach (var table in ODSDB.TableNodes) 
    { 
        currentFileName = string.Format(fileNameTemplate, table.Schema.Name, table.Name);
        System.IO.File.WriteAllText(currentFileName, table.GetDropAndCreateDdl());
    } 
#>   
</Biml>

Tiering

The first neat thing is line 2. I have a directive that tells the biml compiler that this is a tier 1 file. I could have specified tier 3, tier 7, or tier 10, it really doesn't matter as long as this is greater than the value in inc_Connections.biml. Since I didn't specify a tier in that file, it's tier 0. I needed to use an explicit tier here because line 7 references an object in the RootNode (my connection manager) that won't be built until the connections file has been compiled. The take away for tiering: if you're objects in the Biml object tree, you might need to specify tiers to handle build dependencies.

GetDatabaseSchema

Cathrine Wilhelmsen (b|t) did an excellent job covering GetDatabaseSchema so I'll let you read her post and simply comment that this method allowed me to just reverse engineer the schema I was interested.

GetDropAndCreateDdl

The last bit of magic is GetDropAndCreateDdl. It's an extension method that allows me to take the in memory representation of the table and emit the TSQL required to create that object. I enumerate through my TableNodes collection and for each object, I call the GetDropAndCreateDdl method and dump that to a file.

Gist available