t sql to get max length of all nvarchars in a database

Question

Hello,

I am looking for a query that can search an entire database and return max length of all nvarchars in a database. I can write a cursor but its a pretty big database with  lots and lots of columns using nvarchars with most of them having a length of around 300 and doing a

select max(len(nvarchar_col) from table

 from few tables return only 12 or 13. So I want to restrict the length of this data type accordingly.

Thanks 

Answer 1

Take a look at DATALENGTHfunction

CREATE TABLE #t (c NVARCHAR(10)) 

INSERT INTO #t VALUES ('a            ')

SELECT LEN (c),DATALENGTH(c) FROM #t

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

select max(len(nvarchar_col)) from table   

works fine too...but I am looking for a script that goes through each table identify the columns with nvarchar and get their assigned length and the max length in the database.

Answer 3

select  columns.* 
from    information_schema.columns columns
join    information_schema.tables  tables
on      tables.table_name = columns.table_name
where   tables.table_type = 'base table'
order by columns.table_name, columns.column_name

Take a look at CHARACTER_MAXIMUM_LENGTH column

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 4

Great. Modified it slightly and it worked great. Here is the modified version. Thanks

select  tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH,max(len(columns.column_name)) MAX_Length
from    information_schema.columns columns
join    information_schema.tables  tables
on      tables.table_name = columns.table_name
where   tables.table_type = 'base table' and DATA_TYPE='nvarchar'  and columns.CHARACTER_MAXIMUM_LENGTH>50
group by tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH

Answer 5

Great. Modified it slightly and it worked great. Here is the modified version. Thanks

select  tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH,max(len(columns.column_name)) MAX_Length
from    information_schema.columns columns
join    information_schema.tables  tables
on      tables.table_name = columns.table_name
where   tables.table_type = 'base table' and DATA_TYPE='nvarchar'  and columns.CHARACTER_MAXIMUM_LENGTH>50
group by tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH

That won't get you the maximum length of the data. It only gets the maximum length of the potential length of the data in the column. In fact, this code only gets you the length of the column name, not the data.

I would use these to get the data length for a column:

--write some code to loop through each db
exec sp_msforeachdb

--write some code to loop through each table
exec sp_msforeachtable


--and implement this code into the two above as dynamic sql
DECLARE @Columns TABLE (SCH nvarchar(255), TAB nvarchar(255), Col nvarchar(255))
INSERT INTO @Columns SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE DATA_TYPE = 'nvarchar'

SELECT colName, MAX(InRowLength)
FROM AdventureWorks2012.Person.Address with (NOLOCK) 
	CROSS APPLY sys.fn_rowdumpCracker(%%rowdump%%) rc 
	INNER JOIN @Columns C on (rc.colName COLLATE SQL_Latin1_General_CP1_CI_AS = C.COL) and (C.SCH = N'Person') and (C.TAB = N'Address')
GROUP BY colName

I hope you found this helpful, If you did, please mark it as helpful or as the answer!

Answer 6

Yeah. It only gives me the length of field name but not the length of max value of the field value.

Answer 7

Using the sys.fn_rowdumpCracker function allows you to get the data length of every column without having to explicitly type MAX with each and every column. I think that's probably closer to what you were looking for. It's undocumented but you can find several blogs out there about it.

Answer 8

Well this works too.

select  tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH,Max(datalength(columns.COLUMN_NAME)) MAX_Length
from    information_schema.columns columns
join    information_schema.tables  tables
on      tables.table_name = columns.table_name
where   tables.table_type = 'base table' and DATA_TYPE='nvarchar' and columns.CHARACTER_MAXIMUM_LENGTH>50
group by tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH

Answer 9

Well this works too.

select  tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH,Max(datalength(columns.COLUMN_NAME)) MAX_Length
from    information_schema.columns columns
join    information_schema.tables  tables
on      tables.table_name = columns.table_name
where   tables.table_type = 'base table' and DATA_TYPE='nvarchar' and columns.CHARACTER_MAXIMUM_LENGTH>50
group by tables.TABLE_NAME,columns.Column_name,columns.CHARACTER_MAXIMUM_LENGTH

That gets the datalength of the column name itself. Since the column is stored in Unicode (nvarchar) each char will be a length of 2.  

LEN('AddressLine1') = 12 

datalength('AddressLine1') = 24

You'd need to query the table directly to get the length of the data itself.