Finding Datatype

Question

Post reply

Finding Datatype

CrazyMe

Ten Centuries

Points: 1005
More actions
July 12, 2005 at 12:15 am

#165171

how to find the datatype of the column name in the table using query.
Regards
Arun
Kindest Regards,
Arunkumar
Reputation is what other people know about you. Honor is what you know about yourself."--

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Scott Thornburg Hall of Fame Points: 3347 More actions · Answer 1

Arun,

Try something like:

select column_name
      ,data_type
      ,character_maximum_length
      ,numeric_precision
      ,numeric_scale
from   information_schema.columns
where table_name = 'myTable'

Hope this helps,

Scott Thornburg

CrazyMe Ten Centuries Points: 1005 More actions · Answer 2

Soctt,

My requirement was to find the datatype of the column hence i tried the following query

select 'au_id' from information_schema.columns where table_name = 'authors'

I'm using the pubs database and in that using the authors table...and trying to find out the datatype of anyof the column.

But the result weren't as desired.

Regards

Arun

Kindest Regards,

Arunkumar

Reputation is what other people know about you. Honor is what you know about yourself."--

Charles Wilkinson Old Hand Points: 323 More actions · Answer 3

Use:

sp_columns <table name>

Charles Wilkinson, Database Administrator, Sastek Pty Ltd

Kindest Regards,

Charles Wilkinson
Database Administrator
Sastek Pty Ltd
www.sastek.com

CrazyMe Ten Centuries Points: 1005 More actions · Answer 4

Charles

Thanks a ton that worked like a chum...can't we do it for a single column.

Just now learning all the sql commands from books online, do let me know how do i get hands on queries.

Cheers

Arun

Kindest Regards,

Arunkumar

Reputation is what other people know about you. Honor is what you know about yourself."--

sushila SSC-Dedicated Points: 35293 More actions · Answer 5

Arun...is this what you're looking for ?!

select column_name, data_type from information_schema.columns where table_name = 'authors' and column_name = 'au_id'

**ASCII stupid question, get a stupid ANSI !!!**

CrazyMe Ten Centuries Points: 1005 More actions · Answer 6

sushila,

THANKS(big one)!

That solved my 2 day search on books online(this shows how bad i'm), i was trying my hands on columnproperty...

And now from sql commands i'm migrating to combining these commands into queries...do let me know where i can get some practice on queries.

Cheers

Arun

Kindest Regards,

Arunkumar

Reputation is what other people know about you. Honor is what you know about yourself."--

sushila SSC-Dedicated Points: 35293 More actions · Answer 7

You're welcome!

This link should set you off to a good start:

http://www.w3schools.com/sql/default.asp

**ASCII stupid question, get a stupid ANSI !!!**

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Actually this one worked too.. you just had to execute it from the pubs database.

" select column_name

,data_type

,character_maximum_length

,numeric_precision

,numeric_scale

from information_schema.columns

where table_name = 'myTable'

"