Getting all table and schema names containing particular column name

Sometimes, we have a database containing hundreds of tables and we need to find a table containing a particular column name.

In the following SQL, just replace %email% by desired own column name to get the table(s) name in which such column may exist!

SELECT t.name AS table_name,
SCHEMA_NAME(t.schema_id) AS schema_name,
c.name AS column_name,
type.name as column_datatype,
c.max_length AS column_Length
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
JOIN sys.types type ON c.system_type_id=type.system_type_id
WHERE c.name LIKE '%email%' AND type.name <> 'sysname' 
ORDER BY schema_name, table_name;