How to get native column type?

[hmckillop]

Does anyone know if there is a handy proc/function for returning the base type of a column?
I need to return a list of all columns in a table, but only show the actual type as opposed to the User-Defined Data Types.
I know I can look this up in systypes, but I was wondering if there is a built in proc?

Any help appreciated.
Thank you.

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dbomrrsm]

do any of my solutions in thread183-930225 help ?

[blue]DBomrrsm[/blue]

 

[hmckillop]

I found it in what i was doing, but an extension of what you had would do the job. Thanks

SELECT dbo.sysobjects.name AS TableName, dbo.syscolumns.name AS ColName,
 dbo.systypes.name + 
'(' + CONVERT(varchar(4), dbo.syscolumns.length)+ ')', 
--added below two items for the custom and native datatypes
type_name(dbo.systypes.xusertype), type_name(dbo.systypes.xtype)
 AS Datatype
FROM dbo.sysobjects INNER JOIN
dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype
where dbo.sysobjects.name = 'Account'

"I'm living so far beyond my income that we may almost be said to be living apart

 

[dbomrrsm]

no problem

[blue]DBomrrsm[/blue]