Retrieving datalength along with columns and tables in SQL Server query

Question

Because I want to convert the columns to not be varchar(MAX) I want to see the maximum datalength for each column to decide what the new size should be.

I have this query for finding all my (n)varchar(MAX) columns.

SELECT [TABLE_NAME], [COLUMN_NAME]
FROM information_schema.columns
WHERE DATA_TYPE IN ('varchar', 'nvarchar')
  AND CHARACTER_MAXIMUM_LENGTH = -1
ORDER BY TABLE_NAME, COLUMN_NAME

For instance I have a customer table and among the results the following is output for my customers table

+------------+--------------+
| TABLE_NAME | COLUMN_NAME  |
+------------+--------------+
| customers  | name         |
| customers  | address      |
| customers  | postal_code  |
| customers  | city         |
| customers  | email        |
| customers  | phone_number |
+------------+--------------+

By running the following queries:

SELECT MAX(DATALENGTH(name)) FROM customers
SELECT MAX(DATALENGTH(address)) FROM customers
SELECT MAX(DATALENGTH(postal_code)) FROM customers
SELECT MAX(DATALENGTH(city)) FROM customers
SELECT MAX(DATALENGTH(email)) FROM customers
SELECT MAX(DATALENGTH(phone_number)) FROM customers

I can get the result I want, but I'd really like for it to be just one query returning something like:

+------------+--------------+------------+
| TABLE_NAME | COLUMN_NAME  | Datalength |
+------------+--------------+------------+
| customers  | name         |         93 |
| customers  | address      |        122 |
| customers  | postal_code  |          6 |
| customers  | city         |         44 |
| customers  | email        |         75 |
| customers  | phone_number |         18 |
+------------+--------------+------------+

I have tried

SELECT 
    [TABLE_NAME], [COLUMN_NAME], 
    (SELECT MAX(DATALENGTH(COLUMN_NAME))
     FROM TABLE_NAME) AS 'MaxContentLength'
FROM information_schema.columns
WHERE DATA_TYPE IN ('varchar', 'nvarchar')
  AND CHARACTER_MAXIMUM_LENGTH = -1
ORDER BY TABLE_NAME, COLUMN_NAME

But I get this error:

Msg 208, Level 16, State 1, line 1
Invalid object name 'TABLE_NAME'

How do I fix this issue (or is there another way to do what I want?)

Table_name is not a table, its a column. You can't do this in your sub-query. — Arnaud Peralta, Mar 09 '19 at 09:35
According the [documentation](https://learn.microsoft.com/en-us/sql/relational-databases/system-information-schema-views/columns-transact-sql) the CHARACTER_MAXIMUM_LENGTH is -1 for xml and large-value type data. So are you trying to get the max possible length for an XML type? Or something else? For xml it's normally 2GB. [ref](https://learn.microsoft.com/en-us/sql/relational-databases/xml/xml-data-type-and-columns-sql-server?#limitations-of-the-xml-data-type) — LukStorms, Mar 09 '19 at 09:45
@LukStorms I have updated my question to more clearly state what I'm looking for — Daniel, Mar 09 '19 at 09:53

Xabi · Accepted Answer · 2019-03-10T12:27:13.280

This is my approach to solve your question, maybe not the fastest one.

declare @tbl varchar(128), @fld varchar(128)
declare @res table (
    [Table_Name] varchar(128), [Column_Name] varchar(128), [DataLength] int)

declare c1 cursor local for
    select c.TABLE_NAME, c.COLUMN_NAME
    from INFORMATION_SCHEMA.COLUMNS c
    join INFORMATION_SCHEMA.TABLES t on 
        (t.TABLE_CATALOG = c.TABLE_CATALOG and 
         t.TABLE_SCHEMA = c.TABLE_SCHEMA and 
         t.TABLE_NAME = c.TABLE_NAME)
    where t.TABLE_TYPE <> 'VIEW' 
        and c.DATA_TYPE in ('varchar', 'nvarchar') 
        and c.CHARACTER_MAXIMUM_LENGTH = -1
open c1
fetch next from c1 into @tbl, @fld
while @@FETCH_STATUS=0
begin
    insert into @res
    exec ('select '''
          +@tbl+''' as [TABLE_NAME], '''
          +@fld+''' as [COLUMN_NAME], max(datalength('
          +@fld+')) as [DataLength] from '
          +@tbl)
    fetch next from c1 into @tbl, @fld
end    
close c1
deallocate c1

select * from @res

score 0 · Answer 2 · answered Mar 09 '19 at 09:37

Is this correct for you ?

SELECT 
    I.TABLE_NAME, I.COLUMN_NAME, 
    (SELECT MAX(DATALENGTH(COLUMN_NAME))
     FROM information_schema.columns
     WHERE TABLE_NAME = I.TABLE_NAME) AS 'MaxContentLength'
FROM information_schema.columns AS I
WHERE I.DATA_TYPE IN ('varchar', 'nvarchar')
  AND CHARACTER_MAXIMUM_LENGTH = -1
ORDER BY I.TABLE_NAME, I.COLUMN_NAME

LukStorms · Answer 3 · 2019-03-09T10:25:08.060

0

Those multiple queries that use DATALENGTH can be combined in one.

untested notepad scribble

SELECT 
 [TABLE_NAME], 
 [COLUMN_NAME], 
 [Datalength]
FROM 
(
    SELECT 
     'customers' AS [TABLE_NAME], 
     MAX(DATALENGTH(name)) AS name,
     MAX(DATALENGTH(address)) AS address,  
     MAX(DATALENGTH(postal_code)) AS postal_code,
     MAX(DATALENGTH(city)) AS city,
     MAX(DATALENGTH(email)) AS email,
     MAX(DATALENGTH(phone_number)) AS phone_number
    FROM customers
) AS src
UNPIVOT  
(
  [Datalength] 
  FOR [COLUMN_NAME] IN   
  ([name], [address], [postal_code], [city], [email], [phone_number])  
) AS unpvt;

edited Mar 09 '19 at 10:25

answered Mar 09 '19 at 10:17

LukStorms

28,916
5
31
45

Thats fine for the 6 examples in my question, but I have thousands of columns across hundreds of tables. At least I'd need to auto-generate the query for this format to work – Daniel Mar 09 '19 at 21:25
Well, using the information_schema tables it's possible to generate this kind of query via Dynamic Sql, but only for 1 table. And that Dynamic Sql can be generated and executed in a procedure with the tablename as parameter. But 1 query to get all the lengths of all those tables in 1 go, that would quite a monster of a SQL. – LukStorms Mar 09 '19 at 21:41
An example of using Dynamic Sql based on information_schema [here](https://stackoverflow.com/a/54232176/4003419) – LukStorms Mar 10 '19 at 00:19

score 0 · Answer 4 · answered Apr 28 '23 at 13:33

0

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, DATALENGTH(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE IN ('VARCHAR', 'NVARCHAR', 'CHAR', 'NCHAR') AND CHARACTER_MAXIMUM_LENGTH < DATALENGTH(COLUMN_NAME)

answered Apr 28 '23 at 13:33

pabben

341
7
14

Retrieving datalength along with columns and tables in SQL Server query

4 Answers4