Biml: How to get the list of primary key with their datatype

Question

What I was trying to do is to create a staging table only with the primary key from source table.

For example, I have an address table with

create table dbo.Address
(
    AddressId int Primary Key, 
    City varchar(10), 
    State varchar(10)
);

And I want to create the table

CREATE TABLE Staging.AddressPK (AddressId INT PRIMARY KEY);

My Biml script is like this.

CREATE TABLE Staging.<#=tbl.Name#>PK ( <#=tbl.GetColumnList(c => c.IsUsedInPrimaryKey)#> INT PRIMARY KEY);

It is working fine only if the primary key type is INT. So I want to know is there a way to make it dynamic?

Answer 1

The challenge here is the method you're using, GetColumnList is going to return a string (with the column names in it).

You need to work with two elements: column name and the type.

Set up

Here's some static Biml to define our tables. T0 has a composite key of Col1 & Col2. Table T1 has a single column primary key Col0.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
    <Tables>
        <Table Name="T0">
            <Columns>
                <Column Name="Col1" DataType="AnsiString" Length="50" IsNullable="false"></Column>
                <Column Name="Col2" DataType="Date" Length="50" IsNullable="false"></Column>
                <Column Name="Col3" DataType="Int32" Length="50" IsNullable="false"></Column>
            </Columns>
            <Keys>
                <PrimaryKey Name="PK_T0">
                    <Columns>
                        <Column ColumnName="T0.Col1"/>
                        <Column ColumnName="T0.Col2"/>                      
                    </Columns>
                </PrimaryKey>
            </Keys>
        </Table>
        <Table Name="T1">
            <Columns>
                <Column Name="Col0" DataType="AnsiString" Length="50" IsNullable="false"></Column>
                <Column Name="Col2" DataType="Date" Length="50" IsNullable="false"></Column>
                <Column Name="Col3" DataType="Int32" Length="50" IsNullable="false"></Column>
            </Columns>
            <Keys>
                <PrimaryKey Name="PK_T1">
                    <Columns>
                        <Column ColumnName="T1.Col0"/>
                    </Columns>
                </PrimaryKey>
            </Keys>
        </Table>
    </Tables>
</Biml>

Working with the columns collection

This is an incomplete solution. Currently, it emits the data type as the SSIS type but you'll need to tune it for the target database.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#

foreach(AstTableNode tbl in this.RootNode.Tables)
{
    string queryStagingCreate = "";
    System.Text.StringBuilder colList = new System.Text.StringBuilder();
    System.Text.StringBuilder pkConstraint = new System.Text.StringBuilder(string.Format("CONSTRAINT [PK__Staging__{0}] PRIMARY KEY (", tbl.Name));

    // If you need to work with the parts, then use something like this
    var cList = tbl.Columns.Where(c => c.IsUsedInPrimaryKey).Select(c => new {Name = c.Name, DataType = c.DataType}).ToList();
    foreach (var item in cList)
    {
        colList.Append(string.Format("[{0}] {1} NOT NULL,", item.Name, item.DataType));
        pkConstraint.Append(string.Format("[{0}],", item.Name, item.DataType));
    }
    
    queryStagingCreate = string.Format("CREATE TABLE Staging.{0} ({1} {2})); ", tbl.Name, colList.ToString(), pkConstraint.ToString().Substring(0, pkConstraint.Length-1));
    WriteLine("<!-- {0} -->", queryStagingCreate);
}
#>
</Biml>

The output for our sample data is

CREATE TABLE Staging.T0 ([Col1] AnsiString NOT NULL,[Col2] Date NOT NULL, CONSTRAINT [PK__Staging__T0] PRIMARY KEY ([Col1],[Col2]));
CREATE TABLE Staging.T1 ([Col0] AnsiString NOT NULL, CONSTRAINT [PK__Staging__T1] PRIMARY KEY ([Col0]));

There might be Extension methods in the Biml something extensions library that gets you there.

An alternative approach I couldn't make work was make a copy of the existing table and then just remove the columns that weren't in the PK. Or create an empty table and then load it with just the PK columns. Either way, you could then leverage the existing GetDropAndCreateDdl extension method to build the SQL.

// an alternation approach
// Remove the columns that are not the PK and then ask Biml to create the table
// Need a deep copy of tbl. Shallow copies are messing things up
// The calling thread cannot access this object because a different thread owns it
// AstTableNode copyTable = tbl;

AstTableNode copyTable = new AstTableNode(tbl);
copyTable.Columns.AddRange(tbl.Columns.Where(c => c.IsUsedInPrimaryKey));
// Or like this
foreach(AstTableColumnBaseNode acn in copyTable.Columns.Where(c => !c.IsUsedInPrimaryKey))
{
    copyTable.Columns.Remove(acn);
}

WriteLine("<!-- {0} -->", copyTable.GetDropAndCreateDdl());

Answer 2

You should be able to select the data type from INFORMATION_SCHEMA.COLUMNS.