How to find the column max length

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to find the column max length

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-11-10 : 08:32:11

Hello,

i am importing data from excell into database table,after importing i am trying alter the datatypes and length of the table according to column,here how can i know the max length of each column.

Thanx in advance.

Satya

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 08:54:14

select COL_LENGTH(tablename,columname) from INFORMATION_SCHEMA.COLUMNS

PBUH

satya068
Posting Yak Master

233 Posts

Posted - 2010-11-10 : 08:57:54

Thanx Sachin.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-11-10 : 09:04:28

Hello Sachin,

i have tried with ur query but iam getting an error message could you please check the below script
EMPLOYEE is my table name
DGU NUMBER is my column name

select COL_LENGTH(EMPLOYEE,DGU Number) from INFORMATION_SCHEMA.DGU Number

Satya

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-10 : 09:16:22

quote:
Originally posted by satya068

select COL_LENGTH(EMPLOYEE,DGU Number) from INFORMATION_SCHEMA.Columns ~~DGU Number~~

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 09:19:23

quote:
Originally posted by pk_bohra

quote:
Originally posted by satya068

select COL_LENGTH(EMPLOYEE,DGU Number) from INFORMATION_SCHEMA.Columns ~~DGU Number~~

Also you need to quote the tablename and columnname


select COL_LENGTH('EMPLOYEE','DGU Number') from INFORMATION_SCHEMA.Columns DGU Number

PBUH

satya068
Posting Yak Master

233 Posts

Posted - 2010-11-10 : 09:25:57

when i input any column name from the table,i am getting result as 510,i am sure that this is not the max col length!

Satya

Sachin.Nand

2937 Posts

Posted - 2010-11-10 : 09:40:52

[code]
create table t(id int,nm varchar(20))
insert t
select 1,'a'

select distinct COL_LENGTH('t','id'),COL_LENGTH('t','nm') from INFORMATION_SCHEMA.Columns

drop table t

[/code]

PBUH

satya068
Posting Yak Master

233 Posts

Posted - 2010-11-10 : 09:45:31

Thanx sachin.

Satya

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources