Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hoggy
Starting Member
11 Posts |
 Posted - 2012-05-25 : 06:19:58
|
| Hi I am using a script from this site to autogenerate triggers. It uses sys.columns to get table info. My question is about what is held in sys.Columns max_length for nvarchar() columns.Here is the relevant section: SELECT CONVERT(VARCHAR(100), SC.Name) AS FieldName, CONVERT(VARCHAR(50), ST.Name) AS DataType, CONVERT(VARCHAR(10),SC.max_length) AS FieldLength, CONVERT(VARCHAR(10), SC.precision) AS FieldPrecision, CONVERT(VARCHAR(10), SC.Scale) AS FieldScale, CASE SC.Is_Nullable WHEN 1 THEN 'Y' ELSE 'N' END AS AllowNulls FROM Sys.Objects SO INNER JOIN Sys.Columns SC ON SO.object_ID = SC.object_ID INNER JOIN Sys.Types ST ON SC.system_type_id = ST.system_type_id WHERE SO.type = 'u' AND SO.Name = @MyTableIt works more or less perfectly to get columns and datatype and size. But the field length is double the actual size for nvarchar() fields.I want to check if this is always the case or if there may be something else happening I should investigate. Is it simply divide the length by two for nvarchar() |
|
|
hoggy
Starting Member
11 Posts |
Posted - 2012-05-25 : 06:28:21
|
| Think I found answer:"The reason the size is double with the N-Types is that they use a two-byte (USC2) encoding for the Unicode characters they represent." Although this is refering to storage size rather than sys.columns, but I assume sys.columns is representing this. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-05-25 : 10:08:46
|
I prefer using INFORMATION_SCHEMA.COLUMNS view which has all these details in it and doesnot require any other objects to be joinedSELECT COLUMN_NAME,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,NUMERIC_PRECISION,NUMERIC_SCALE,IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @MyTable ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
syscolumn length