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?)
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