How to get the data type of each column?

[jasonsalas]

I'm totally not in the know about SQL 2000 programming, so is there a system SPROC I can run or something else that will tell me the data type of columns in a table?

 

[hmckillop]

sp_helptext <tablename> returns a list of columns within the specified table, alternatively you can look in syscolumns with something like
select
syc.name as 'ColumnName', syt.name as 'DataType', syc.length as 'DataLength',
syc.xprec as 'Precision', xyc.xscale as 'Scale' from syscolumns syc
inner join systypes syt on syt.xtype = syc.xtype
where id = (Object_id(<tablename>))


"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[jasonsalas]

hmmm...I tried running

sp_helptext myTable

...but Query Analyzer returned:
Server: Msg 15197, Level 16, State 1, Procedure sp_helptext, Line 94
There is no text for object 'myTable'.

 

[jasonsalas]

...and same for:

EXEC sp_helptext myTable
EXEC sp_helptext 'myTable'

 

[hmckillop]

sorry it should sp_help <tablename> or sp_helptext <proc_name>



"Own only what you can carry with you; know language, know countries, know people. Let your memory be your travel bag.

 

[jasonsalas]

a-ha! thanks!

 

[john76]

Also:

SELECT  *
  FROM  <database>.Information_Schema.Columns
  WHERE  TABLE_NAME = <table>

HTH,
John

 

[philhege]

John76's code is the recommended approach, since the next flavor of SQL Server may re-structure the sys* tables. MS guarantees that the INFORMATION_SCHEMA views will continue to work across versions; however, the sys* tables are caveat emptor.

Phil Hegedusich
Senior Web Developer
IIMAK

http://www.iimak.com

-----------
Boy howdy, my Liberal Studies degree really prepared me for this....
-----------
A skeleton walks into a bar, and says "I'll have a beer and a mop.