CODEWITHSUNDEEP
sqlsql-server-2012maxmaxlengthadhoc-queries
user3329160
user3329160

Reputation: 165

A query to get the MAX LEN of every field in a SQL table

I've been trying to come up with a query that will list the name of every column in a table with it's max length.

This is in SQL Server 2012.

Any help is greatly appreciated.

Upvotes: 1

Views: 5896

Answers (2)

Anuj Tripathi
Anuj Tripathi

Reputation: 2281

You can use below query to get the the Actual and used length of the columns in a table

 DECLARE @TSQL VARCHAR(MAX) = ''
DECLARE @TableName sysname = 't1'
SELECT @TSQL = @TSQL + 'SELECT ' + QUOTENAME(sc.name, '''') + ' AS ColumnName, ' + QUOTENAME(t.name, '''') + ' AS DataType, ' +
QUOTENAME(sc.max_length, '''') + ' AS ActualLength, MAX(DATALENGTH(' + QUOTENAME(sc.name) + ')) AS UsedLength FROM '+@TableName+ char(10) +' UNION '
FROM sys.columns sc
        JOIN sys.types t on t.system_type_id = sc.system_type_id and t.name != 'sysname'
WHERE sc.OBJECT_ID = OBJECT_ID(@TableName)
SET @TSQL = LEFT(@TSQL, LEN(@TSQL)-6)
EXEC(@TSQL)

Upvotes: 4

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48187

If you want know your table detail use information_schema.columns

select * 
from information_schema.columns 
where table_name = 'YourTableName'

If you want the lenght of a string field use LEN

select len(field1)
from YourTableName

Upvotes: 1

Related Questions