SQL Server Performance Forum – Threads Archive

Find Max Length of column value

Hi,
I am trying to develop a stored procedure to find maximum length of data value in each column in all tables in a db. I created a cursor to get all column names and I use this
FETCH NEXT FROM a_cursor INTO @field
to get column name but when I use this @field in this
select @field, max(len(@field)) from <table> it does not give me the data length, instead it gives me the length of @field,
if @field is ‘id’, I get length = 2, and not column data length. Can any tell me what is wrong? Is there a better way to do what I am doing? Reagrds
Nick

Have you ensured that you only query string columns? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstuetze PASS Deutschland e.V. http://www.sqlpass.de)

Yes, this particular table I am working with has several columns each nvarchar(255)!
I know they all don’t need to be, and there are other tables where I have to do the same. Regards
Nick

You have to spell out column names in SQL. The only way around this is to use dynamic SQL, where you create the query statement as a string where you concatenate the varying column names, and then you execute the string. Since you want to gather the data in a rowset, you’ll need a temp table … DECLARE @table SYSNAME, @field SYSNAME set @table = <your table name goes here> CREATE TABLE #Temp (TableName SYSNAME, ColumnName SYSNAME, MaxLength INT) DECLARE a_cursor CURSOR STATIC
FOR
SELECT name FROM syscolumns WHERE id = object_id(@table)
OPEN a_cursor FETCH NEXT FROM a_cursor INTO @field
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO #temp
EXEC (‘select ”’ + @table + ”’, ”’ + @field + ”’, max(len(‘ + @field + ‘)) from ‘ + @table )
FETCH NEXT FROM a_cursor INTO @field
END CLOSE a_cursor
DEALLOCATE a_cursor SELECT * FROM #Temp DROP TABLE #Temp

Good point, Adriaan! Missed that dynamic stuff here. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

Excellent!! exactly what I was looking for. Thanks abunch, you guys are awesome! Nick

Now I ran into a problem, some of the columns have nulls, and I get this error: Server: Msg 409, Level 16, State 2, Line 1
The maximum aggregate operation cannot take a void type data type as an argument.
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of len function. I did turned off ANSI warning and on after the drop table line. How can I ignore if one of the column has all null data?

max(case when column is null then -1 else len(column) end)

Brilliant! Thanks for the code Adriaan. It helped me make quick work of a task that lay ahead of me. Kudos.

Good to see people searching through old postings. Even better to see my old script being put to good use!

Another way of doing the same isdeclare @sql varchar(8000), @table sysname
select
@table=’your_table’, @sql=”select
@[email protected]+’select ”’[email protected]+”’,”’+column_name+”’, max(len(‘+column_name+’)) from ‘[email protected]+’ union all ‘ from information_schema.columns where table_name=”[email protected]+”set
@sql=left(@sql,len(@sql)-9)exec
(@sql)

Hi! Please help me..
I run script and get an error
Server: Msg 2715, Level 16, State 3, Procedure sp_filedefrag, Line 6 Column or parameter #-2: Cannot find data type SYSNAME. Parameter ‘@name’ has an invalid data type.
Yuri

[quote user="Yuri"]
Hi! Please help me..
I run script and get an error
Server: Msg 2715, Level 16, State 3, Procedure sp_filedefrag, Line 6 Column or parameter #-2: Cannot find data type SYSNAME. Parameter ‘@name’ has an invalid data type.
Yuri
[/quote]
Which version of SQL Server are you using?
Post the actual code used in the procedure

SQL Server 2000 Enterprise Edition
Product vers 8.00.2039(SP4)

This is begining of script…
CREATE PROCEDURE [dbo].[sp_filedefrag]
AS
SET NOCOUNT ON
SET XACT_ABORT ON
DECLARE
@sql VARCHAR(1000),
@name SYSNAME,
@msg VARCHAR(1000)
CREATE TABLE #t (
[name] SYSNAME,
fileid SMALLINT,
[filename] NCHAR(260),
[filegroup] SYSNAME NULL,
[size] NVARCHAR(18),
[maxsize] NVARCHAR(18),
growth NVARCHAR(18),
usage VARCHAR(9)
)

You might try replacing SYSNAME with NVARCHAR(128) – but it’s unclear why SYSNAME is breaking the code.
You’re not by any chance using OPENQUERY against a server that is not MS SQL Server?

Thanks for help Adriaan.
Yuri

Yuri – glad to be of help, but did you find out what the problem was, and how did you resolve it?

]]>