field's datatype - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

field's datatype

Author

Topic

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-02-23 : 20:09:05

Hello everyone. How to make a query that returns the datatype of a field.

Thanks & Best Regard.

-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 20:42:42

[code]
select table_name = object_name(c.id),
column_name = c.name,
data_type = t.name, c.length, c.xprec, c.xscale
from syscolumns c inner join systypes t
on c.xtype = t.xtype
where c.id = object_id(<table name>)
and c.name = <column name>
[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2007-02-23 : 21:29:40

have a look at information_schema.columns

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-02-23 : 23:04:04

i have a table name = customers
fields = dcno, custname, address, telno, age, status

If I use your code, I got error that says "'customers' is not a recognized built-in function name."

This is the query.

Select table_name = customers(c.dcno),
column_name = c.custname,
data_type = t.custname, c.length, c.xprec, c.xscale
from syscolumns c inner join systypes t
on c.xtype = t.xtype
where c.dcno = customers_dcno(customers)
and c.custname = custname

-Ron-

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 23:07:23

you only changed those in italics. object_name() and object_id() is a build in function

and enclosed your table name / column name in single quote

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-02-23 : 23:13:03

or use the INFORMATION_SCHEMA.COLUMNS as suggested by Nigel.


select 	*
from 	INFORMATION_SCHEMA.COLUMNS
where	TABLE_NAME	= 'customers'
and	COLUMN_NAME	in ('dc_no', 'custname', 'address', 'telno', 'age', 'status')

cwtriguns2002
Constraint Violating Yak Guru

272 Posts

Posted - 2007-02-23 : 23:55:08

Fantastic. Thanks.

-Ron-

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-02-25 : 07:19:46

or may be ..

sp_help 'customers'

Chirag

http://chirikworld.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources