CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
user31353
user31353

Reputation: 43

T-SQL: How can I get user-defined data-type of the column by its ID?

I have a 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

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

Upvotes: 2

Views: 9167

Answers (1)

DJ.
DJ.

Reputation: 16257

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

Upvotes: 6

Related Questions