Get column type from SYSCOLUMNS?

25 Aug 2006 7:18 PM

Rick Charnes

Hi, when I select from the SYSCOLUMNS system table, I see various
numeric values in column XTYPE that I believe refer to the data type of
the column. What do these values refer to? Do I need to join to
another system table somewhere to see their actual (string) values?

25 Aug 2006 7:26 PM

Aaron Bertrand [SQL Server MVP]

SELECT
[Table_Name] = OBJECT_NAME(c.id),
[Column_Name] = c.name,
c.xtype,
[Column_Type] = t.name
FROM syscolumns c
INNER JOIN systypes t
ON c.xtype = t.xtype
ORDER BY 1, 2

Show quote

"Rick Charnes" <rickxyz--nospam.zyxcharnes@thehartford.com> wrote in message
news:MPG.1f5916ef622e089c98994b@msnews.microsoft.com...
> Hi, when I select from the SYSCOLUMNS system table, I see various
> numeric values in column XTYPE that I believe refer to the data type of
> the column. What do these values refer to? Do I need to join to
> another system table somewhere to see their actual (string) values?

26 Aug 2006 9:30 PM

Erland Sommarskog

Aaron Bertrand [SQL Server MVP] (ten.xoc@dnartreb.noraa) writes:

> SELECT
> [Table_Name] = OBJECT_NAME(c.id),
> [Column_Name] = c.name,
> c.xtype,
> [Column_Type] = t.name
> FROM syscolumns c
> INNER JOIN systypes t
> ON c.xtype = t.xtype
> ORDER BY 1, 2

Add:

WHERE t.xusertype < 255

else you will get duplicate rows if there are columns with a user-
defined type.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx