Get column list and max length in that field

[litton1]

Hi all,
Hi all, I am trying to get a list of all columns in a table (done) and then find the maximum length of the field that is used. For example if I have a table called Foo and there is a varchar field called Bar. I want to know what the allowed length of that column is and what actual space is being used by the string. So say bar has just values ‘Y’ in it, output should look like. (I can format the output..Just need help getting the data. )

Table Foo - Bar - > SQL Length = 500 - Actual max length used = 1

select
(syscolumns.length) as [Length], (select MAX(LEN( syscolumns.name))from Foo ) AS MaxColLengthUsed, -- this bit not work
   syscolumns.name as [Column],
   syscolumns.xusertype as [Type],
   sysobjects.xtype as [Objtype],*
from 
   sysobjects, syscolumns    
where sysobjects.id = syscolumns.id
and   sysobjects.xtype = 'u'
and   sysobjects.name = 'Foo'
order by syscolumns.name

[/code

Age is a consequence of experience

 

[simian336]

Unfortuantely SQL server won't allow you to use the output field of a query as the input to the same query.

What you will need to is create temp table with the max lenths of each field then join it to your query by table name and field name.

A quick google should find several scripts to find the max length of each field. Here is a pretty good one. She uses dynamic sql and Unions. She does not put it in a temp table but you could addapt that pretty quickly.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/maximum-length-of-data-in

Simi

 

[simian336]

Unfortuantely SQL server won't allow you to use the output field of a query as the input to the same query.

What you will need to is create temp table with the max lenths of each field then join it to your query by table name and field name.

A quick google should find several scripts to find the max length of each field. Here is a pretty good one. She uses dynamic sql and Unions. She does not put it in a temp table but you could addapt that pretty quickly.

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/maximum-length-of-data-in

Simi

 

[litton1]

Thanks. I will give it a go.

Age is a consequence of experience