How to find out (runtime) if a type in t-sql is fixed-length or not?

Question

Is it possible to find out runtime using t-sql if a type (e.g. nvarchar or int) is fixed-length or not by querying some system-table?

The reason I need to do this is that I need to generate sql-code runtime and need to generate some declarations (DECLARE @foo SOMETYPE(LENGTH) or DECLARE @foo SOMETYPE) depending on the type of some columns (that are unknown at design-time).

My current bet is that I can check if sys.all_columns.max_length = sys.types.max_length and if so assume that it is fixed-length (since max_length seems to have a special code (-1), at least for nvarchar).

Answer 1

You can look at information_schema.columns. If a character typed column has a max length it will be specified in character_maximum_length, numeric fields have their precision defined in numeric_precision. A couple notes, to the best of my knowledge the int type is always 4 bytes, also, any of the var types are by definition not fixed length, and only store up to their max length, if a nvarchar(10) holds a 2 character string, it only stores 2 characters. So to detect fixed length strings, you would be looking for the char or nchar types.

Answer 2

Yes, it's possible. Look at the code for the built-in stored proc sp_columns to see how to do this.