如何在一条 SQL 语句中获取 varchar[n] 字段的大小？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5705316/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get the size of a varchar[n] field in one SQL statement?
提问by Vivian River
Suppose that I have a SQL table that has a varchar[1000] field called "Remarks".
假设我有一个 SQL 表，它有一个名为“备注”的 varchar[1000] 字段。
I would like to craft a single SQL statement, which when executed, will return 1000, or whatever the size of the varchar field might be changed to in the future.
我想编写一个 SQL 语句，执行时将返回 1000，或者将来可能更改 varchar 字段的大小。
Something like SELECT size(Remarks) FROM mytable.
类似的东西SELECT size(Remarks) FROM mytable。
How do I do this?
我该怎么做呢？
回答by Neil Knight
select column_name, data_type, character_maximum_length
from information_schema.columns
where table_name = 'myTable'
回答by MarcE
On SQL Server specifically:
具体在 SQL Server 上：
SELECT DATALENGTH(Remarks) AS FIELDSIZE FROM mytable
回答by Frank
For SQL Server (2008 and above):
对于 SQL Server（2008 及更高版本）：
SELECT COLUMNPROPERTY(OBJECT_ID('mytable'), 'Remarks', 'PRECISION');
COLUMNPROPERTY returns information for a column or parameter (id, column/parameter, property). The PRECISION property returns the length of the data type of the column or parameter.
COLUMNPROPERTY 返回列或参数（id、列/参数、属性）的信息。PRECISION 属性返回列或参数的数据类型的长度。
回答by Greg Quinn
This will work on SQL SERVER...
这将适用于 SQL SERVER...
SELECT COL_LENGTH('Table', 'Column')
回答by ProVega
I was looking for the TOTAL size of the column and hit this article, my solution is based off of MarcE's.
我正在寻找列的总大小并点击了这篇文章，我的解决方案基于 MarcE 的。
SELECT sum(DATALENGTH(your_field)) AS FIELDSIZE FROM your_table
回答by Ihor Konovalenko
This is a function for calculating max valid length for varchar(Nn):
这是一个用于计算 varchar(Nn) 的最大有效长度的函数：
CREATE FUNCTION [dbo].[GetMaxVarcharColumnLength] (@TableSchema NVARCHAR(MAX), @TableName NVARCHAR(MAX), @ColumnName VARCHAR(MAX))
RETURNS INT
AS
BEGIN
RETURN (SELECT character_maximum_length FROM information_schema.columns
WHERE table_schema = @TableSchema AND table_name = @TableName AND column_name = @ColumnName);
END
Usage:
用法：
IF LEN(@Name) > [dbo].[GetMaxVarcharColumnLength]('person', 'FamilyStateName', 'Name')
RETURN [dbo].[err_Internal_StringForVarcharTooLong]();
回答by Andrei Sura
For t-SQL I use the following query for varchar columns (shows the collation and is_null properties):
对于 t-SQL，我对 varchar 列使用以下查询（显示排序规则和 is_null 属性）：
SELECT
s.name
, o.name as table_name
, c.name as column_name
, t.name as type
, c.max_length
, c.collation_name
, c.is_nullable
FROM
sys.columns c
INNER JOIN sys.objects o ON (o.object_id = c.object_id)
INNER JOIN sys.schemas s ON (s.schema_id = o.schema_id)
INNER JOIN sys.types t ON (t.user_type_id = c.user_type_id)
WHERE
s.name = 'dbo'
AND t.name IN ('varchar') -- , 'char', 'nvarchar', 'nchar')
ORDER BY
o.name, c.name
回答by mrbengi
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'Table1'
