SQL: List tables with certain column names

In a project I'm working on I have the need to get a list of all the tables that have a certain column name.  Most SQL dialects have system tables and in MS-SQL you can do it in the following way:

SELECT t.name, c.name FROM sys.tables t
JOIN sys.columns c ON c.object_id = t.object_id
WHERE c.name = 'prop_id'
ORDER BY t.name

Please consult your documentation for the proper syntax for the system table and system column tables.