Clever Geek Handbook

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

How to find all tables or views with a column name that matches a pattern.

The pattern is a simple pattern LIKE %abcd%

, not a regular expression.

The query or queries must return both views and tables.

+3


source to share


1 answer


dbc.ColumnsV stores information about columns:

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


This can return stored routines or macros as well, so you'd better join 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') 
;
+9


source






More articles:


All Articles