How can I select matching column names between multiple tables?

I have close to 100 tables and a lot have very similar names, beginning with names like ‘STANDARD’ and ‘MARKIT’, and so on and so forth. I’d like to find all column names that are the same for these tables. So, I would like to search all tables that start with ‘STANDARD’ have the same field names, like 'Field1', 'Field2', 'Field3', and 'Field4'. I’m guessing it would be some combination of sys.columns.name and sys.tables, but I don’t know for sure. I think the SQL below is a good start, but I won’t know what the common columns are before I run the script.

SELECT
  sys.columns.name AS ColumnName,
  tables.name AS TableName
FROM
  sys.columns
JOIN sys.tables ON
  sys.columns.object_id = tables.object_id
WHERE
  tables.name like '%STANDARD%' AND ... something else ...

Answer

You were on the right track, you need to join sys.tables with sys.columns and here I’m using a trick to attempt to find the columns and group by table name, keeping only the groups where all the fields were found (untested)

SELECT      t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name LIKE '%STANDARD%' and c.name in ('Field1', 'Field2', 'Field3', 'Field4')
GROUP BY t.name
HAVING count(*) = 5
ORDER BY t.name

EDIT

The following query finds column names that are shared accross multiple tables

select c.ColumnName, count(*)
from sys.columns c
join sys.tables t
on c.object_id = t.object_id
group by c.ColumnName
having count(*) > 2;

You can use the idea above as a subquery if needed.