Getting a column's Length in SQL 1

[jonbatts]

I'm writing a user interface in VB.NET for users to enter information into a Microsoft SQL Server database. When a user enters say, a machine name into a textbox I want the textbox to accept no more characters than can be entered in that field in the database. What statement do I use to retrieve the length for that column so I can set the textboxes maxLength at runtime. Thanks a lot.

 

[mrdenny]

This will give you the max length for text columns.

select Column_Name, Character_Maximum_Length
from information_schema.columns
where table_name = '{TableName}'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chaoma]

in sql

LEN(string_expression)

 

[amrita418]

Look into using the information_schema.columns view. You can add a case stmt to get the character_maximum_length or numeric_precision or datetime_precision depending on data type of the column.

Regards,
--aa

 

[jonbatts]

I'm sorry mrdenny but could you specify which strings in your code are ones I'm supposed to replace with values from my database please? Thanks.

 

[Catadmin]

You only need to replace TABLENAME with a value from your database. The rest you can use as is.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jonbatts]

Thanks so much for your help mrdenny. Worked like a charm.

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[NoCoolHandle]

Mr Denny

<snip>length for text columns.</snip>

Don't you mean Char or VarChar?

I didn't think there was any limit to the amount of space you can use in a text (well 2gig) column.

Sorry, just nit picking. I tend to go to sysindexes (but I love diving the system tables and avoid information_schema views like the plague.- so just ignore me

 

[mrdenny]

The query I posted shows the maximum amount of data the field is configured for. It doesn't matter what data type it's setup for.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[chiph]

Don't forget to account for the differences between CHAR and NCHAR. In Unicode the character count != byte count.

Chip H.


____________________________________________________________________
If you want to get the best response to a question, please read FAQ222-2244 first