1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3329160
  • 165
  • 2
  • 13

2 Answers2

4

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)
Anuj Tripathi
  • 2,251
  • 14
  • 18
  • Hello, I tried to use this query and I got an error message: Msg 537, Level 16, State 5, Line 8 Invalid length parameter passed to the LEFT or SUBSTRING function. – user3329160 Oct 05 '15 at 17:47
  • @User3329160 That's because you haven't provided the table name for which you want details.. Update "t1" with your table name here **DECLARE TableName sysname = 't1'** – Anuj Tripathi Oct 07 '15 at 10:55
  • 1
    This worked for me... if you're using a schema other than 'dbo', make sure to specify: DECLARE @TableName sysname = '[XYZ].[Table_Name]' Probably wouldn't hurt to put your database name in before that as well. – MayTheSForceBeWithYou Mar 11 '19 at 16:33
1

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
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118
  • 1
    Thanks. So the character_maximum length field only contains -1 for all of the fields that contain characters. (varchar(max)). How can I get the actual max length of what's in the table? – user3329160 Sep 25 '15 at 18:51
  • 1
    I'm looking for a dynamic way to list every field name and it's corresponding max length via a single query. – user3329160 Sep 25 '15 at 19:04
  • Then you have to create a dynamic query using the `information_schema.columns`, do you know how to create a dynamic query? – Juan Carlos Oropeza Sep 25 '15 at 19:09
  • No, I do not know how to. – user3329160 Sep 25 '15 at 19:14
  • Then you ask the [wrong question](http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx) before you ask how create a query to dynamic calculate len, you need to ask how create dynamic query. Check something like [THIS](http://www.codeproject.com/Articles/20815/Building-Dynamic-SQL-In-a-Stored-Procedure) Isn't easy one, but hope you can understand the basic concepts there. – Juan Carlos Oropeza Sep 25 '15 at 19:22