Menu
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.
325
Lets write a below SQL query to compare column name between these two tables. SELECT * into #tblA FROM information_schema. columns WHERE table_Schema ='dbo' and table_name = 'TableA' ; SELECT * into #tblB FROM information_schema. columns WHERE table_Schema ='dbo' and table_name = 'TableB' ; SELECT COALESCE(A.
How to list all tables that contain a specific column name in MySQL? You want to look for tables using the name of columns in them. SELECT DISTINCT TABLE_NAME FROM INFORMATION_SCHEMA. COLUMNS WHERE COLUMN_NAME IN('column1', 'column2') AND TABLE_SCHEMA = 'schema_name';
select table_name from all_tab_columns where column_name = 'PICK_COLUMN'; If you've got DBA privileges, you can try this command instead: select table_name from dba_tab_columns where column_name = 'PICK_COLUMN'; Now if you're like me, you may not even know what the column you're searching for is really named.
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')
;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
