blocks|key|711351|text|select+column_name,+data_type,+character_maximum_length++++
++from+information_schema.columns++
+where+table_name+=+'myTable'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|711352|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select column_name, data_type, character_maximum_length 
 from information_schema.columns 
 where table_name = 'myTable'
</code></pre>

blocks|key|927595|text|特别是在SQL+Server上：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|927596|SELECT+DATALENGTH(Remarks)+AS+FIELDSIZE+FROM+mytable|code-block|syntax|javascript|927597|Documentation|offset|length|927598|entityMap|0|LINK|mutability|MUTABLE|url|http://msdn.microsoft.com/en-us/library/ms173486.aspx^0|0|0|0|D|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@$I|V|J|W|1|X]]|A|$]]|$1|K|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|L|$M|$5|N|O|P|A|$Q|R]]]]

On SQL Server specifically: 

<pre><code>SELECT DATALENGTH(Remarks) AS FIELDSIZE FROM mytable
</code></pre>

<a href="http://msdn.microsoft.com/en-us/library/ms173486.aspx" rel="noreferrer">Documentation</a>

blocks|key|927900|text|对于SQL+Server+(2008及更高版本)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|927901|SELECT+COLUMNPROPERTY(OBJECT_ID('mytable'),+'Remarks',+'PRECISION');|code-block|syntax|javascript|927902|COLUMNPROPERTY返回列或参数(id、列/参数、属性)的信息。PRECISION属性返回列或参数的数据类型的长度。|927903|COLUMNPROPERTY+documentation|offset|length|927904|entityMap|0|LINK|mutability|MUTABLE|url|https://docs.microsoft.com/en-us/sql/t-sql/functions/columnproperty-transact-sql^0|0|0|0|0|S|0|0^^$0|@$1|2|3|4|5|6|7|U|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|X|8|@]|9|@$K|Y|L|Z|1|10]]|A|$]]|$1|M|3|-4|5|6|7|11|8|@]|9|@]|A|$]]]|N|$O|$5|P|Q|R|A|$S|T]]]]

For SQL Server (2008 and above):

<pre><code>SELECT COLUMNPROPERTY(OBJECT_ID('mytable'), 'Remarks', 'PRECISION');
</code></pre>

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.

<a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/columnproperty-transact-sql" rel="noreferrer" title="COLUMNPROPERTY documentation">COLUMNPROPERTY documentation</a>

blocks|key|927797|text|这将在SQL+SERVER上工作...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|927798|SELECT+COL_LENGTH('Table',+'Column')|code-block|syntax|javascript|927799|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

This will work on SQL SERVER...

<pre><code>SELECT COL_LENGTH('Table', 'Column')
</code></pre>

blocks|key|711489|text|我正在寻找专栏的总大小，然后点击了这篇文章，我的解决方案是基于MarcE的。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|711490|SELECT+sum(DATALENGTH(your_field))+AS+FIELDSIZE+FROM+your_table|code-block|syntax|javascript|711491|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>SELECT sum(DATALENGTH(your_field)) AS FIELDSIZE FROM your_table
</code></pre>

blocks|key|927763|text|这是一个计算varchar(Nn)的最大有效长度的函数：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|927764|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|code-block|syntax|javascript|927765|用法：|927766|IF+LEN(@Name)+>+[dbo].[GetMaxVarcharColumnLength]('person',+'FamilyStateName',+'Name')+
++++++++++++RETURN+[dbo].[err_Internal_StringForVarcharTooLong]();|927767|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

This is a function for calculating max valid length for varchar(Nn):

<pre><code>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
</code></pre>

Usage: 

<pre><code>IF LEN(@Name) &gt; [dbo].[GetMaxVarcharColumnLength]('person', 'FamilyStateName', 'Name') 
 RETURN [dbo].[err_Internal_StringForVarcharTooLong]();
</code></pre>

blocks|key|711637|text|对于t-SQL，我对varchar列使用以下查询(显示排序规则和is_null属性)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|711638|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|code-block|syntax|javascript|711639|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

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

<pre><code>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
</code></pre>

blocks|key|927850|text|select+column_name,+data_type,+character_maximum_length++++
from+INFORMATION_SCHEMA.COLUMNS
where+table_name+=+'Table1'|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|927851|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>select column_name, data_type, character_maximum_length 
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'Table1'
</code></pre>

blocks|key|711762|text|对于MS+SQL+Server，这将返回列长度：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|711763|SELECT+COL_LENGTH('dbo.mytable',+'Remarks')+AS+Result;|code-block|syntax|javascript|711764|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

For MS SQL Server this will return column length:
<pre><code>SELECT COL_LENGTH('dbo.mytable', 'Remarks') AS Result;
</code></pre>

Suppose that I have a SQL table that has a varchar[1000] field called "Remarks".

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.

Something like <code>SELECT size(Remarks) FROM mytable</code>.

How do I do this?

How to get the size of a varchar[n] field in one SQL statement?

SQL Server

假设我有一个SQL表，它有一个名为“varchar1000”的备注字段。我想手工制作一条SQL语句，它在执行时将返回1000，或者是varchar字段的大小将来可能更改为的任何值。就像SELECT size(Remarks) FROM mytable一样。我该怎么做呢？

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

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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

回答 9

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

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