Get column lengths using SQL Server Management Objects (SMOs)

Question

I am writing T4 templates for generating CRUD stored procedures, etc.

I iterate over 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 how to find the length of a varchar column? No property is specified in the column Length / MaxLength etc.

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

Answer 1

The Column type has a DataType property that contains these bits of information that you are looking for:

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

etc. Not all fields are filled in for each type, obviously - the numerical scale on VARCHAR does not make sense ....

Check out the MSDN docs for accuracy, scalability, and maximum length . The main sentence is as follows:

Accuracy is the number of digits per number. Scale is the number of digits to the right of the decimal point in the number. For example, the number 123.45 has an accuracy of 5 and a scale of 2.

Thus, DECIMAL(12,4) has an accuracy of 12 digits (total), of which 4 after the decimal point (scale ), and therefore 8 digits is a decimal point.

But these should be the fields you are looking for, right?

Answer 2

Try:

 column.Properties["Length"].Value 

Answer 3

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

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