SQL Server - Selecting Max Len of all columns

Asked By naomi_williams via SQLMonster.com on 27-Jul-07 04:22 PM
The basic script I am running is Select Max(Len(*))
I want to view all of the max column lengths. The error I get is
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
What am I doing wrong?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200707/1




Tom Moreau replied on 27-Jul-07 04:34 PM
How about showing us the statement you ran that gave you this error?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


The basic script I am running is Select Max(Len(*))
I want to view all of the max column lengths. The error I get is
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '*'.
What am I doing wrong?

--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200707/1
naomi_williams via SQLMonster.com replied on 27-Jul-07 04:42 PM
Select max(len(*)) From OHM_BAE.dbo.RefTo


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200707/1
MikeWhitin replied on 27-Jul-07 04:50 PM
LEN(*) is invalid syntax. You cannot use LEN() in this manner. You would have
to do something like:

select TotalLen=LEN(<col1>) + LEN(<col2>) + ... + LEN(<coln>)

Keep in mind that LEN would not count trailing blanks in a character
column(s). You would need to use the DATALENGTH() funciton for that.

-Mike
Tom Moreau replied on 27-Jul-07 04:50 PM
The problem is the *.  You need to specify max (len(<column name>)) for each
column:

select
max (len (ColA))
,    max (len (ColB))
,    max (len (ColC))
from
OHM_BAE.dbo.RefTo


--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON   Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


Select max(len(*)) From OHM_BAE.dbo.RefTo


--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-programming/200707/1
naomi_williams via SQLMonster.com replied on 27-Jul-07 04:56 PM
I have over 50 columns, and I am trying to do this efficiently. Would
Datalength work in this way?


--
Message posted via http://www.sqlmonster.com
Shuurai replied on 27-Jul-07 05:18 PM
On Jul 27, 4:22 pm, "naomi_williams via SQLMonster.com" <u36096@uwe>

For the LEN function in this manner you need to specify each column.
If you have a lot of columns to get through, you can run through all
of them one by one with a cursor. In the example below, change the
'foo' to the name of your table.

declare @columns table (column_name char(40))
declare @table char(50), @statement nchar(100), @current nchar(20)
set nocount on

set @table = 'foo'

insert @columns
select
b.[Name]
from
sys.all_objects a
inner join sys.all_columns b on
a.[object_id] = b.[object_id]
where
a.[Name] = @table

declare cur_columns cursor for select column_name from @columns
open cur_columns
fetch next from cur_columns into @current
while @@fetch_status <> -1
begin
set @statement = N'select max(len([' + rtrim(@current) + N'])) from '
+ @table
select @current
exec sp_executesql @statement
fetch next from cur_columns into @current
end
rpresser replied on 27-Jul-07 05:30 PM
Datalength returns the number of bytes used to represent an
expression. You, on the other hand, don't yet have an expression at
all -- you want it to be automatically deduced from all the columns in
the table, without you having to provide anything but a table name.
There is no particularly easy way to do this.


On Jul 27, 4:56 pm, "naomi_williams via SQLMonster.com" <u36096@uwe>
Erland Sommarskog replied on 29-Jul-07 07:33 PM
naomi_williams via SQLMonster.com (u36096@uwe) writes:

Inventing your own syntax.

There is no shortcut. The statement must list len() for all columns.
Possibly you could generate it:

SELECT ''' + name + ''', len(' + name + ') FROM tbl'
FROM   sys.columns
WHERE  object_id = object_id('tbl')

(This generates one statement per column.)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
MikeWhitin replied on 27-Jul-07 07:00 PM
Here is something that does what you want. You will have to modify it to suit
your needs. A view gets created that is simple to work with. The view can be
dropped and re-created by re-running the proc. The proc could be extended to
include every table in your database.

This proc creates a view consisting of a table's column
names and the max datalength for that column. The proc
returns the name of the view that it created. A simple
query can then be run to determine the largest datalength
value in the view.

alter proc CreateViewMyMaxColLen @pTbl varchar(100)
as
declare @tbl varchar(100), @col varchar(100), @colid int, @cmd varchar(8000)
select @cmd = 'Create View v' + @pTbl + 'MaxColLen as '
declare TblColCsr cursor for
select o.name, c.name, colid
from syscolumns c join sysobjects o on c.id=o.id and o.type='U' and
o.name=@pTbl
order by c.id, colid
open TblColCsr
fetch next from TblColCsr into @tbl, @col, @colid
while(@@fetch_status = 0)
begin
select @cmd=@cmd + 'select Col=' + quotename(@col,'''') +
',MaxLen=(select max(DATALENGTH(' + @col + ')) from ' + @tbl + ')' +
case when @colid<>(select max(colid) from syscolumns where
id=object_id(@pTbl)) then ' union all ' else '' end
fetch next from TblColCsr into @tbl, @col, @colid
end
close TblColCsr
deallocate TblColCsr
print @cmd
exec(@cmd)
select 'v' + @pTbl + 'MaxColLen'
go


exec CreateViewMyMaxColLen '<table name>'

select col,maxLen from vtblUsersMaxColLen
where maxlen=(select max(MaxLen) from vtblUsersMaxColLen)

Like they said, there is no easy around it.

-Mike