Hi, is there a way to retrieve the data type of table columns? the information in SYSCOLUMN are not enough, because they don't tell whether the column is CHAR or INT or DATE.

Even in SYSOBJECT I couldnt find such info.

The only way to get this info is DESCRIBE TABLENAME, but I need to inquire the data type inside a procedure, so I need it as select statement.

asked 26 Nov '20, 03:21

Baron's gravatar image

Baron
1.4k91107129
accept rate: 47%


Ah, found it, the view SYSCOLUMNS has the full answer!!

permanent link

answered 26 Nov '20, 04:19

Baron's gravatar image

Baron
1.4k91107129
accept rate: 47%

Well, for current versions, system view SYSTABCOL would be the natural choice - note, it returns variable-sized types including their declared width, such as "char(1)" or "varchar(128)".

Using SYSCOLUMNS tends to be error-prone IMHO as there might be two such views, one owned by SYS and the other by dbo...

permanent link

answered 26 Nov '20, 05:15

Volker%20Barth's gravatar image

Volker Barth
37.6k347514778
accept rate: 34%

converted 26 Nov '20, 05:19

Yes, I preceed it with the owner name, so I use SYS.SYSCOLUMNS.

Thank you

(26 Nov '20, 09:07) Baron
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×32
×31

question asked: 26 Nov '20, 03:21

question was seen: 296 times

last updated: 26 Nov '20, 09:07