February 8, 2011 at 8:06 am
I'm trying to create a script that will check if a column has an explicate length. For instance if I have a table that looks like this:
Create table TestTable(
Col1 int,
Col2 varchar(20),
Col3 decimal(10,5),
Col4 datetime
)
I want to write a query that will return Col2 and Col3. Is that information available somewhere?
Thanks.
February 8, 2011 at 8:21 am
Check out sys.columns.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 8, 2011 at 10:30 am
This will give you the column name, table name, datatype and the maximum length:
select c.name as columnname, o.name as tablename, d.name as datatype, c.max_length
from sys.columns c inner join sys.objects o on c.object_id = o.object_id
inner join sys.types d on c.system_type_id = d.system_type_id
where o.type = 'u'
order by o.name
February 8, 2011 at 12:00 pm
You can use "INFORMATION_SCHEMA.COLUMNS" as well.
February 8, 2011 at 12:04 pm
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply