If you are using the DacFx api, it is easy to get a table, you just create a TSqlModel object with your model in and call GetObject or GetObjects and you can get to your TSqlObject table, then to get to the list of columns there are a couple of ways you can do it, you can call the table’s GetChildren which returns all the children on the table and in amongst that lot is one or more Columns:

foreach (var child in table.GetChildren())
{
Console.WriteLine(child.Name + ” : ” + child.ObjectType.Name);
}

This will print out a list of all the children on the table, the Name property is the column name and the ObjectType is the type of object, we want columns so we need to check that ObjectType is the same as ModelSchema.Column. As an aside, the ModelSchema class is very useful as it is a way to query for specific types or to know what type of TSqlObject it is you have. If you need to find out how something works, go to the ModelSchema to get started!

The second way is to query the API using GetReferencedRelationshipInstances which lets you specify the type of objects that you want, in this case we just want the Columns relationships and because we have a Table object, we use the Table.Columns ModelRelationshipClass which returns all the Columns that this table directly references:

foreach (var child in table.GetReferencedRelationshipInstances(Table.Columns))

When we query for relationships, we get a list of relationships (who would have guessed), a relationship looks something like this:

FromObject – this is the table we asked for the references columns from

Object – this is the TSqlObject for the column

ObjectName – the name of the column

Relationship – describes how they are related, in this case it is actually the Table.Columns class (get used to seeing things go round and round in circles with this api!)

Now we have the columns we can query each one to find the actual data type, to do this we query the Column for it’s referenced objects, this time not a related object like a column to a table but just something that is referenced by the column, so we use Column.DataType which gets us an IEnumerable list of the 1 datatype that the column is:

var types = child.Object.GetReferenced(Column.DataType)

The Name property of the type gives the column type such as varchar or tinyint and then the object has some properties you can query to get whether the column is nullable or a max datatype and the length, scale and precision:

var type = child.Object.GetReferenced(Column.DataType).FirstOrDefault();
var isNullable = type.GetProperty<bool?> (DataType.UddtNullable);
var length = type.GetProperty<int?>(DataType.UddtLength);

I guess the important thing to know here is that you should always know what type of TSqlObject you have and as long as you query the API using the GetReferenced* and GetProperty methods and you use the ModelSchema class to get the top level objects, it is easy to get to the properties you need, so in this example using ModelSchema.Table, then Table.Columns then Column.DataType and finally DataType.XX you can easily get the column type.

In my example I enumerate all the objects, it can be simplified (I am not sure I mean simplified!) using linq and something like:

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

(In case you recognise the table – Yes I do use a dacpac for AdventureWorks for testing!)

As always there is a demo project in github https://github.com/GoEddie/DacFxApi – this example is the ShowTableColumns example.

 

Enjoy!