Search code examples
sql-serversmo

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

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

Solution

  • 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?