If you write code to SQL Server then you might be interested in this: I have written a tSQLt tdd training course which has helped over 500 people learn both tSQLt and how to apply TDD practices to their SQL Server T-SQL development, you can join the course at https://courses.agilesql.club.

DacFx how to get the data type from a column a discussion of properties, relationships and the TSqlModel

Reading Time: 15 minutes (it is quite hard going, but you need to know this if you need to work with the DacFx)

DacFx - Getting the data type of a column on a table, a rehash of an old blog post

This post has been years in the making, I did a version of it years ago here: https://sqlserverfunctions.wordpress.com/2014/09/27/querying-the-dacfx-api-getting-column-type-information - well fast forward 5 years to now and I can now understand how it works and whereas before I basically sat in a debugger trying to figure out what method to use. I am now pretty confident I can get to any property I need using excel rather than using a debugger and reflection, this makes me happy :)

In the original post I said to get the data type from a column on a table, do this:

var dataType = table.First()
.GetReferencedRelationshipInstances(Table.Columns)
.First(p => p.ObjectName.ToString() == “[dbo].[AWBuildVersion].[SystemInformationID]”)
.Object.GetReferenced(Column.DataType)
.First()
.Name;

Note, I don’t recommend only using one line of linq to do everything but it was just a suggestion :)

The things to point out here are that table is a TSqlObject which has various methods for getting to properties:

  • GetChildren
  • GetProperty
  • GetReferenced
  • GetReferencedRelationshipInstances
  • GetReferencing
  • GetReferencingRelationshipInstances

These all come in various flavours, all a little sour. In the above snippet it uses GetReferencedRelationshipInstances to get all the relationships that have a ModelRelationshipClass of Table.Columns, that is Table.Columns is an object of type ModelRelationshipClass - this is important so bear with me. If we look at Table.Columns we see this:

This shows that Table.Columns is an object, a class of type ModelRelationshipClass and it shows the type is belongs to, the FromObjectClass which is set to Table, then we have the name of the property, in this case Columns, then we see that th erelationship class has no properties and it is supported on All platforms (Sql versions, not the cool new x-plat support) and finally the relationship type: Composing.

So what does this all mean? Well, to understand how all this works we need to think about what it is we are dealing with here. The TSqlModel is a representation of how the database should look, how tables are defined, which columns they have, what stored procedures are doing. Then we need to understand how the model is used - The model is used to compare to other models and also traverse the hierarchy, two different use cases:

Traversing the hierarchy

This is where we have things like in SSDT where you can use the refactoring support to rename a table, all the references to that table are found and updated - the model is used to find those relationships so the model needs to support finding out an object’s dependent things but also what the object is depended on by.

Comparing models

This is the second use case, if we have two models - what changes are there that need to happen? This isn’t just a “does prox x look the same as proc y”, that would be easy - when you throw in options like ignore whitespace, or for tables, ignore column order then it becomes clearer that the model needs to be able to compared to another model but the deep context information like are two tables the same but with their columns in a different order. The requirements for something that can handle this quickly outgrow a string comparison.

Relationships

The model is made up of TSqlObject's which are generic, an object could be a table or a view or a data type and it will be a TSqlObject - now these objects are very, very specific - a table for example doesn’t have any columns or a column doesn’t have a data type, this is an example of a Column:

The public properties on this example are the Name and the ObjectType that is it, super focused. However, what we do have is a set of relationships and properties - now these have always been a bit of a mystery to me but I have uploaded a csv to github with the current list of properties and relationships that every TSqlObject in the DacFx has today, grab the entire csv from:

If we look at Column then we see a column has a whole load of properties such as Collation which returns a sting or DataType which isn’t a property but a relationship.

ObjectType RelationshipType RelationshipName Return Type Platform
Column property Collation String All
Column property IsIdentityNotForReplication Boolean SqlAzureV12
Column property Nullable Boolean All
Column property IsRowGuidCol Boolean SqlAzureV12
Column property Sparse Boolean Sql100
Column property Expression SqlScriptProperty All
Column property Persisted Boolean All
Column property PersistedNullable System.Boolean? All
Column property Scale Int32 All
Column property Precision Int32 All
Column property Length Int32 All
Column property IsMax Boolean All
Column property XmlStyle XmlStyle All
Column property EncryptionAlgorithmName String SqlAzureV12
Column property EncryptionType Int32 SqlAzureV12
Column property GeneratedAlwaysType ColumnGeneratedAlwaysType SqlAzureV12
Column property GraphType Int32 SqlAzureV12
Column property IdentityIncrement String All
Column property IdentitySeed String All
Column property IsFileStream Boolean Sql100
Column property IsHidden Boolean SqlAzureV12
Column property IsIdentity Boolean All
Column property IsPseudoColumn Boolean SqlAzureV12
Column property MaskingFunction String SqlAzureV12
Column relationship ExpressionDependencies Peer All
Column relationship XmlSchemaCollection Peer All
Column relationship DataType Peer All
Column relationship ColumnEncryptionKey Peer SqlAzureV12

To get a property such as Length we can call GetProperty, because we know from the chart that Length returns an Int32 we can use the typed version of GetProperty and ask for the property that has the name Length so:

var length = column.GetProperty<int>(Column.Length);

Length is an int of the actual length. Now it isn’t all roses, if we wanted to know something more exotic such as the expression on a calculated column we could do:

var expression = column.GetProperty<SqlScriptProperty>(Column.Expression);

However we will get an issue compiling because SqlScriptProperty is an internal sealed class in the DacFx. In this case we have to fall back to using the generic version of GetProperty like column.GetProperty(Column.Expression); - through experimentation I can see that this returns a string of the definition, there seem to be a mix of types that are internal and public, I would like it if this was a bit clearer (I have raised a github issue here, I know the ssdt team look at these issues so maybe something might happen: https://github.com/Microsoft/DACExtensions/issues/27

So, if we take a computed column we can get to the definition which I suppose covers the “comparing model” cases, but what about traversing references? For that we can use ExpressionDependencies so if we have a computed column called a which performs a calculation on another column we can use the relationship ExpressionDependencies to find the columns that a refers to.

Insted of using GetProperty, we need to get the details of a relationship so can either just get the TSqlObject of the referenced columns or we can get the TSqlObject and the details of the relationship:

Just the TSqlObject of the referenced columns:

column.GetReferenced(Column.ExpressionDependencies)

Full details of the relationships to the referenced columns:

column.GetReferencedRelationshipInstances(Column.ExpressionDependencies, DacQueryScopes.UserDefined)

I would think that in most case GetReferenced would be enough - I would say that GetReferencedRelationshipInstances is more for when we are enumerating objects and we don’t know the type - in that you want to see what an object references rather than saying “i need the length of this type”.

What are Column.Expression and Column.ExpressionDepenencies

Well these are important and we need to understand about the difference between properties and relationships and how we use GetProperty as opposed to either GetReferenced or GetReferencedRelationshipInstances.

GetProperty takes a ModelPropertyClass and the GetReferenc* methods take a ModelRelationshipClass - you can’t ask GetProperty to get a relationship or visa-versa. I think this is what confused be the most about the DacFx, not understanding how these two similar classes were so different and led to so many hours spent with a debugger trying to figure out whether to use GetProperty or GetReferenc*.

Column.Expression therefore is a ModelPropertyClass and Column.ExpressionDependencies is a ModelRelationshipClass.

A worked example

So lets take a TSqlObject of type Table and see how we can get to the data types using the csv I uploaded to github: https://github.com/GoEddie/DacFx-Info/blob/master/DacFxRelationships/ModelSchema.csv

We first query a model for a table:

var model = new TSqlModel("Database1.dacpac");
var table = model.GetObject(ModelSchema.Table, new ObjectIdentifier("dbo", "table_with_computed"), DacQueryScopes.UserDefined);

The table object is a TSqlObject:

Now we have a TSqlObject we can use our csv to see what properties and relationships exist for a Table object:

ObjectType RelationshipType RelationshipName Return Type Platform
Table property AnsiNullsOn System.Boolean? All
Table property ChangeDataCaptureEnabled Boolean Sql100
Table property ChangeTrackingEnabled Boolean Sql100
Table property FileStreamNull System.Boolean? Sql100
Table property LargeValueTypesOutOfRow Boolean All
Table property QuotedIdentifierOn System.Boolean? All
Table property TableLockOnBulkLoad Boolean All
Table property TrackColumnsUpdated Boolean Sql100
Table property VardecimalStorageFormatEnabled Boolean SqlAzureV12
Table property MemoryOptimized Boolean Sql120
Table property RowCount System.Int64? All
Table property DataSize System.Double? All
Table property IndexSize System.Double? All
Table property DataPages System.Int64? All
Table property UsedPages System.Int64? All
Table property Durability Durability Sql120
Table property IsAutoGeneratedHistoryTable Boolean All
Table property IsEdge Boolean SqlAzureV12
Table property IsNode Boolean SqlAzureV12
Table property IsReplicated Boolean OnPremises
Table property LockEscalation LockEscalationMethod Sql100
Table property RemoteDataEnabled Boolean Sql130
Table property RetentionUnit Int32 SqlAzureV12
Table property RetentionValue Int32 SqlAzureV12
Table property TextInRowSize Int32 All
Table relationship Columns Composing All
Table relationship DataCompressionOptions Composing Sql100
Table relationship Filegroup Peer SqlAzureV12
Table relationship FilegroupForTextImage Peer OnPremises
Table relationship FileStreamFilegroup Peer Sql100
Table relationship FileStreamPartitionScheme Peer Sql100
Table relationship PartitionColumn Peer SqlAzureV12
Table relationship PartitionScheme Peer SqlAzureV12
Table relationship Schema Hierarchical All
Table relationship TemporalSystemVersioningHistoryTable Peer SqlAzureV12

In this list we see we have a relationship called Columns so we call:

foreach (var column in table.GetReferenced(Table.Columns, DacQueryScopes.UserDefined))
{

}

Now we need to have a bit of a guess here and hope it pans out - where the relationship name is plural DataCompressionOptions,Columns, etc, the type returned in an enumerable of the non-plural version so an enumerable of DataCompressionOption or Column - all the ones I have tried fit this but it isn’t covered by the compiler so who knows /shrug :).

IF we take this to be true, column is a TSqlObject of type Column so we then look up the properties on Column:

ObjectType RelationshipType RelationshipName Return Type Platform
Column property Collation String All
Column property IsIdentityNotForReplication Boolean SqlAzureV12
Column property Nullable Boolean All
Column property IsRowGuidCol Boolean SqlAzureV12
Column property Sparse Boolean Sql100
Column property Expression SqlScriptProperty All
Column property Persisted Boolean All
Column property PersistedNullable System.Boolean? All
Column property Scale Int32 All
Column property Precision Int32 All
Column property Length Int32 All
Column property IsMax Boolean All
Column property XmlStyle XmlStyle All
Column property EncryptionAlgorithmName String SqlAzureV12
Column property EncryptionType Int32 SqlAzureV12
Column property GeneratedAlwaysType ColumnGeneratedAlwaysType SqlAzureV12
Column property GraphType Int32 SqlAzureV12
Column property IdentityIncrement String All
Column property IdentitySeed String All
Column property IsFileStream Boolean Sql100
Column property IsHidden Boolean SqlAzureV12
Column property IsIdentity Boolean All
Column property IsPseudoColumn Boolean SqlAzureV12
Column property MaskingFunction String SqlAzureV12
Column relationship ExpressionDependencies Peer All
Column relationship XmlSchemaCollection Peer All
Column relationship DataType Peer All
Column relationship ColumnEncryptionKey Peer SqlAzureV12

From this list, to get the data type we can see a relationship called DataType (2nd from bottom) so we prod that:

foreach (var column in table.GetReferenced (Table.Columns, DacQueryScopes.UserDefined))
{
   var type = column.GetReferenced(Column.DataType, DacQueryScopes.All);
}

This then gives use a type which is TSqlObject of type DataType:

ObjectType RelationshipType RelationshipName Return Type Platform
DataType property UddtNullable Boolean All
DataType property UddtIsMax Boolean All
DataType property UddtLength Int32 All
DataType property UddtPrecision Int32 All
DataType property UddtScale Int32 All
DataType property SqlDataType SqlDataType All
DataType relationship Schema Hierarchical All
DataType relationship Type Peer All

DataType itself has a property SqlDataType (which incidentally isn’t internal to the DacFx):

foreach (var column in table.GetReferenced (Table.Columns, DacQueryScopes.UserDefined))
{
   var type = column.GetReferenced(Column.DataType, DacQueryScopes.All);
   var sqlDataType = type.FirstOrDefault().GetProperty<SqlDataType>(DataType.SqlDataType);
   Console.WriteLine(sqlDataType);
}

The SqlDataType is even documented at microsoft (not much in the DacFx is!!):

https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dac.model.sqldatatype?view=sql-dacfx-140.3881.1

Fin

So there you have it, a rehash of a really old blog post but hopefully with less hand waving than last time - I know this post was a bit niche but if you need to work with the DacFx, the more of this sort of thing, the better if you ask me :)