How to find all tables / views with a column name that matches a particular pattern

Question

How do I find all tables or views that have a column name that matches a pattern.

The pattern is a simple LIKE %abcd% pattern and not a regex.

The query or queries should return both views and tables.

Answer 1

dbc.ColumnsV stores column information:

SELECT DatabaseName, TableName, ColumnName
FROM dbc.ColumnsV
WHERE ColumnName LIKE '%abcd%'
;

This might also return Stored Prodedures or Macros, so you might better join to dbc.TablesV:

SELECT t.DatabaseName, t.TableName, t.TableKind, ColumnName
FROM dbc.TablesV AS t JOIN dbc.ColumnsV AS c
  ON t.DatabaseName = c.DatabaseName
 AND t.TableName = c.TableName 
WHERE ColumnName LIKE '%abcd%'
AND TableKind in ('T','V') 
;