Find column detail

declare @fieldName VARCHAR(50)
Set @fieldName = '%ColumnName%'
-----------------------------------
with cte1 as
(
SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
            ,TY.[name] AS 'DataType'
FROM        sys.columns c
JOIN        sys.tables  t ON c.object_id = t.object_id
join sys.types ty on c.system_type_id = ty.system_type_id and c.user_type_id = ty.user_type_id 
WHERE       c.name LIKE @fieldName
)
, cte2 as
(            
SELECT      QUOTENAME(sOBJ.name) AS [TableName]
 , SUM(sPTN.Rows) AS [RCount]
FROM        sys.objects AS sOBJ
 INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE       sOBJ.type = 'U'
 AND sOBJ.is_ms_shipped = 0x0
 AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY    sOBJ.schema_id
 , sOBJ.name
)
, cte3 as
(
select t1.ColumnName
 , t1.TableName
 , '['+t1.TableName+']' as TName
 , t1.DataType 
from cte1 as t1
)

select t1.ColumnName
 , t1.TableName
 , t1.DataType
 , t2.RCount
from cte3 as t1
 inner join cte2 as t2
 on t1.TName= t2.TableName
--where RCount > 0
order by t1.TableName