T-SQL: How to get user-defined data-type of the column by it's ID or smth?

Question

I have the query like this

SELECT TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE
FROM MY_DB.INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME = 'OrderId'
ORDER BY TABLE_NAME
GO

OrderId column is of user-defined data type. But DATA_TYPE in the query shows underlying system type (i.e. bigint). How to show user-defined type name?

Answer 1

You can change the Where clause:

SELECT 
    c.name as ColumneName, 
    c.colid As ColumnOrder, 
    st.name as UserType,
    bt.name as BaseType
FROM dbo.syscolumns c
INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
WHERE c.id = OBJECT_ID('TableName')
ORDER BY c.colid

EDIT - more complete version - Enjoy!!

SELECT 
    OBJECT_NAME(c.id) as TableName,
    c.name as ColumnName, 
    c.colid As ColumnOrder, 
    st.name as UserType,
    bt.name as BaseType
FROM 
    dbo.syscolumns c
    INNER JOIN dbo.systypes st ON st.xusertype = c.xusertype
    INNER JOIN dbo.systypes bt ON bt.xusertype = c.xtype
WHERE 
    OBJECTPROPERTY(c.id,'ISTABLE') = 1
    AND c.name = 'ColumnName'
ORDER BY
    OBJECT_NAME(c.id), 
    c.colid