Get length of Columns using SQL Server Management Objects (SMO)

Question

I'm writing T4 templates to generate CRUD stored procs etc

I am looping through the columns of a table using SMO:

For Each column As Column In table.Columns
    WriteLine("@" & column.Name & " " & column.DataType.Name & ", ")
Next

My question is simply how do I find the Length of a varchar column? There doesn't appear to be any Length / MaxLength etc property on the column.

I'm using http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.column_members.aspx as a reference

Centaurus

Answer 1

The Column type has a property called DataType which contains these bits of information you're looking for:

int maxLen = column.DataType.MaximumLength;
int maxPrecision = column.DataType.NumericPrecision;
int numericScale = column.DataType.NumericScale;

and so on. Not all fields are filled for every type, obviously - numeric scale on a VARCHAR doesn't make sense....

Check out the MSDN docs on precision, scale and max length. The main sentence is this:

Precision is the number of digits in a number. Scale is the number of digits to the right of the decimal point in a number. For example, the number 123.45 has a precision of 5 and a scale of 2.

So a DECIMAL(12,4) has a precision of 12 digits (total), of which 4 are after the decimal point (the scale) and thus 8 digits are before the decimal point.

But those should be the fields you're looking for, right?

Unsearchable

Answer 2

Try:

column.Properties["Length"].Value

Dimple

Answer 3

You can use the following code to get all the field properties:

<#= propertyType #>
<#=col.DataType.MaximumLength#>
<#=col.Nullable?"?":""#>

Inspiration