SELECT <non-pivoted column>,  
    [first pivoted column] AS <column name>,  
    [second pivoted column] AS <column name>,  
    ...  
    [last pivoted column] AS <column name>  
FROM  
    (<SELECT query that produces the data>)   
    AS <alias for the source query>  
PIVOT  
(  
    <aggregation function>(<column being aggregated>)  
FOR   
[<column that contains the values that will become column headers>]   
    IN ( [first pivoted column], [second pivoted column],  
    ... [last pivoted column])  
) AS <alias for the pivot table>  
<optional ORDER BY clause>;

SELECT c.name,
       t.name,
       c.max_length,
       c.precision,
       c.scale
FROM   SYS.COLUMNS c
       JOIN SYS.TYPES t
         ON t.user_type_id = c.user_type_id
            AND t.system_type_id = c.system_type_id
WHERE  object_id = OBJECT_ID('TABLENAME/VIEWNAME')  

-- Search tables with specified column name
SELECT c.name AS 'ColumnName',
       t.name AS 'TableName' 
  FROM sys.columns c 
  JOIN sys.tables t ON c.object_id = t.object_id 
 WHERE c.name LIKE '%MyName%' 
 ORDER BY TableName ,ColumnName;