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

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?

From stackoverflow

• 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
  

  : I need to list all tables containing column with this name. How can I update your example to do that?