查询表、视图字符串字段的长度

SELECT a.colorder 字段序号,
a.name 字段名,
b.name 类型,
CASE
WHEN COLUMNPROPERTY(a.id, a.name, 'PRECISION') = -1 THEN
'，长度无限制'
ELSE
'，长度限制' + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS NVARCHAR(20)) + '个字符'
END 长度
FROM syscolumns a
LEFT JOIN systypes b
ON a.xtype = b.xusertype
INNER JOIN sysobjects d
ON a.id = d.id
AND d.xtype = 'V'
AND d.name <> 'dtproperties'
WHERE d.name = 'View_MeetingSFH_Hly'
AND
(
b.name = 'varchar'
OR b.name = 'Nvarchar'
)
ORDER BY d.name,
a.id,
a.colorder;

 

SELECT a.colorder 字段序号,
a.name 字段名,
b.name 类型,
CASE
WHEN COLUMNPROPERTY(a.id, a.name, 'PRECISION') = -1 THEN
'，长度无限制'
ELSE
'，长度限制' + CAST(COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS NVARCHAR(20)) + '个字符'
END 长度
FROM syscolumns a
LEFT JOIN systypes b
ON a.xtype = b.xusertype
INNER JOIN sysobjects d
ON a.id = d.id
AND d.xtype = 'U'
AND d.name <> 'dtproperties'
WHERE d.name = 'Meeting'
AND
(
b.name = 'varchar'
OR b.name = 'Nvarchar'
)
ORDER BY d.name,
a.id,
a.colorder;