USE MYDB
GO

DECLARE @Value NVARCHAR(128);
SELECT  @Value = 'ID'; -- Nombre a buscar

SELECT  OBJECT_NAME(object_id) AS object_name,
        c.name AS column_name,
        TYPE_NAME(c.user_type_id) AS user_type_name,
        c.max_length
FROM    sys.columns AS c
        INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE   c.name like '%'+@Value+'%' and OBJECT_NAME(object_id) not like 'syn%'

Buscar tablas por nombre de columnas;

Buscar tablas por nombre de columnas

SQL