如何在一个 SQL 语句中获得 varchar [ n ]字段的大小？

假设我有一个 SQL 表，其中有一个 varchar [1000]字段，名为“ Remmarks”。

我想精心制作一个 SQL 语句，它执行时将返回1000，或将来可能更改 varchar 字段大小的任何值。

就像 SELECT size(Remarks) FROM mytable。

我该怎么做？

183684 次浏览
小开
最佳答案 
select column_name, data_type, character_maximum_length
from information_schema.columns
where table_name = 'myTable'
小开

On SQL Server specifically: 

SELECT DATALENGTH(Remarks) AS FIELDSIZE FROM mytable

Documentation

小开

I was looking for the TOTAL size of the column and hit this article, my solution is based off of MarcE's.

SELECT sum(DATALENGTH(your_field)) AS FIELDSIZE FROM your_table
小开

This is a function for calculating max valid length for 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]();
小开

This will work on SQL SERVER...

SELECT COL_LENGTH('Table', 'Column')
小开

For t-SQL I use the following query for varchar columns (shows the collation and is_null properties):

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
小开 
select column_name, data_type, character_maximum_length
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'Table1'
小开

For SQL Server (2008 and above):

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 documentation

小开

For MS SQL Server this will return column length:

SELECT COL_LENGTH('dbo.mytable', 'Remarks') AS Result;
小开

For SQL Server, instead of SIZE we can use LEN rest everything given in question is fine. LEN returns number of characters, not sure I should call it eye friendly or brain friendly :)

SELECT LEN(Remarks) FROM mytable

DATALENGTH also works fine as given in other answers but it returns number of bytes.

SELECT DATALENGTH(Remarks) FROM mytable

Here is documentation reference


提交答案