How to find which tables have specific column names in SQL Server

There are times you are required to find a specific column name and what tables are linked up with similar column names. This is easier with third party tools available however if your organisation does not support any third party tool you might think this is more like a manual work. Of course not! Below is the query to get you there. Try and see :)

SELECT t.name AS table_name,
SCHEMA_NAME(schema_id) AS schema_name,
c.name AS column_name
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.name LIKE '%avail%'
ORDER BY schema_name, table_name;