Import Metadata with GetDatabaseSchema

Why Import Metadata at All

Most Biml projects start with the same step: load metadata from a source database so the generator knows which tables and columns to work with. The recommended entry point is GetDatabaseSchema. The method runs against a connection node and returns an ImportResults object that exposes the source as SchemaNodes (schema metadata) and TableNodes (table and view metadata) ready to use inside BimlScript loops.

Method Signatures and Parameters

GetDatabaseSchema can be called with no parameters, with a single ImportOptions value, or with three parameters. With no parameters it imports every schema, table, and view on the connection. The other overloads narrow the import or skip object types that are not needed.

Method	Description
GetDatabaseSchema()	Import every schema, table, and view.
GetDatabaseSchema(ImportOptions)	Import every schema, table, and view, excluding object types named in ImportOptions.
GetDatabaseSchema(IncludedSchemas, IncludedTables, ImportOptions)	Import only the listed schemas and tables, excluding object types named in ImportOptions.

The parameters accepted by these overloads:

Parameter	Description	Examples
ImportOptions	Object types to exclude.	ImportOptions.None, ImportOptions.ExcludeViews, ImportOptions.ExcludePrimaryKey, ImportOptions.ExcludeForeignKey, ImportOptions.ExcludeIndex, ImportOptions.ExcludeColumnDefault, ImportOptions.ExcludeCheckConstraint, ImportOptions.ExcludeIdentity, ImportOptions.ExcludeUniqueKey
IncludedSchemas	Schemas to import.	null, new List<string>(), new List<string>{ "Sales" }
IncludedTables	Tables or views to import.	null, new List<string>(), new List<string>{ "Customer", "Order", "OrderLine" }

A null collection means no filter on that axis: pass null for IncludedTables to import every table within the listed schemas, or null for IncludedSchemas to import a list of tables across any schema.

A Worked Example

The example below imports metadata from a sales source database, then uses TableNodes to drive a package that drops and recreates each table in a staging database. GetDropAndCreateDdl is called on each table node to emit the matching SQL:

<#
  var srcConnection = SchemaManager.CreateConnectionNode(
    "OpsSource",
    @"Data Source=DataServer;Initial Catalog=SalesOps;Provider=SQLNCLI11;Integrated Security=SSPI;");
  var srcMetadata = srcConnection.GetDatabaseSchema();
#>

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
  <Connections>
    <OleDbConnection Name="StagingDb" ConnectionString="Data Source=DataServer;Initial Catalog=SalesStaging;Provider=SQLNCLI11;Integrated Security=SSPI;" />
  </Connections>
  <Packages>
    <Package Name="DropCreateStagingTables" ConstraintMode="Linear">
      <Tasks>
        <# foreach (var stgTable in srcMetadata.TableNodes) { #>
          <ExecuteSQL Name="Drop and Create <#=stgTable.Schema#> <#=stgTable.Name#>" ConnectionName="StagingDb">
            <DirectInput><#=stgTable.GetDropAndCreateDdl()#></DirectInput>
          </ExecuteSQL>
        <# } #>
      </Tasks>
    </Package>
  </Packages>
</Biml>

The same iteration shape works for SELECT generation, INSERT scripting, view creation, or any other per table emission.

Common Filtering Patterns

Excluding object types keeps the imported metadata small and avoids pulling in indexes or constraints when only the table shape is needed:

<#
  var srcMetadata = srcConnection.GetDatabaseSchema(ImportOptions.ExcludeViews);
#>

Multiple ImportOptions can be combined with bitwise or:

<#
  var srcMetadata = srcConnection.GetDatabaseSchema(
    ImportOptions.ExcludeCheckConstraint
    | ImportOptions.ExcludeColumnDefault
    | ImportOptions.ExcludeForeignKey
    | ImportOptions.ExcludeIdentity
    | ImportOptions.ExcludeIndex
    | ImportOptions.ExcludePrimaryKey
    | ImportOptions.ExcludeUniqueKey
    | ImportOptions.ExcludeViews);
#>

Schema and table filters narrow the import to a known list:

<#
  var schemaList = new List<string>{ "Sales" };
  var tableList = new List<string>{ "Customer", "Order", "OrderLine" };
  var srcMetadata = srcConnection.GetDatabaseSchema(schemaList, tableList, ImportOptions.None);
  var srcOrders = srcConnection.GetDatabaseSchema(null, tableList, ImportOptions.None);
  var srcSales = srcConnection.GetDatabaseSchema(schemaList, null, ImportOptions.None);
#>

How GetDatabaseSchema Compares to ImportTableNodes and ImportDB

ImportTableNodes and ImportDB also pull metadata, but they only support wildcard filters. They can import every table whose name starts with "Order", but they cannot pick the three named tables Customer, Order, and OrderLine. GetDatabaseSchema accepts explicit lists of schemas and tables and tends to perform better than the older methods on large source databases.

Summary

GetDatabaseSchema is the standard entry point for pulling source metadata into Biml. It can return everything on the connection or be narrowed to specific schemas, specific tables, or specific object types. The returned TableNodes feed directly into the BimlScript loops that generate packages, scripts, and views.

Why Import Metadata at All​

Method Signatures and Parameters​

A Worked Example​

Common Filtering Patterns​

How GetDatabaseSchema Compares to ImportTableNodes and ImportDB​

Summary​