SQL Server Performance

Find Max Length of column value

Discussion in 'General Developer Questions' started by hnazee01, Mar 15, 2006.

  1. hnazee01 New Member

    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

  2. FrankKalis Moderator

  3. hnazee01 New Member

    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
  4. Adriaan New Member

    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
  5. FrankKalis Moderator

    Good point, Adriaan! Missed that dynamic stuff here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a><br />Ich unterstuetze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)
  6. hnazee01 New Member

    Excellent!! exactly what I was looking for.

    Thanks abunch, you guys are awesome!

    Nick
  7. hnazee01 New Member

    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?
  8. mmarovic Active Member

    max(case when column is null then -1 else len(column) end)
  9. cmacguire New Member

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

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

    Another way of doing the same isdeclare @sql varchar(8000), @table sysname
    select
    @table='your_table', @sql=''select
    @sql=@sql+'select '''+@table+''','''+column_name+''', max(len('+column_name+')) from '+@table+' union all ' from information_schema.columns where table_name=''+@table+''set
    @sql=left(@sql,len(@sql)-9)exec
    (@sql)
  12. Yuri New Member

    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
  13. Madhivanan Moderator

    [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
  14. Yuri New Member

    SQL Server 2000 Enterprise Edition
    Product vers 8.00.2039(SP4)
  15. Yuri New Member

    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)
    )
  16. Adriaan New Member

    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?
  17. Yuri New Member

    Thanks for help Adriaan.
    Yuri
  18. Adriaan New Member

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

Share This Page