cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
cancel
Showing results for 
Search instead for 
Did you mean: 

How do I search for all the columns/field names starting with "XYZ"

QuicKick
New Contributor

I would like to do a big search on all field/columns names that contain "XYZ".

I tried below sql but it's giving me an error.

SELECT 

table_name

,column_name

FROM information_schema.columns

WHERE column_name like '%<account>%'

order by table_name, column_name

ERROR states "Table or view not found: information_schema.columns; line 4, pos 5"

2 REPLIES 2

Anonymous
Not applicable

@Ian Fox​ :

The error you are receiving suggests that the database you are working with does not have an information_schema.columns table or the user you are logged in as does not have permissions to access it. The information_schema.columns table is a system catalog view that contains information about all columns in all tables in a database, including their names. It is commonly used to search for column names. To search for columns that contain "XYZ", you can use the following SQL statement:

SELECT table_name, column_name 
FROM information_schema.columns 
WHERE column_name LIKE '%XYZ%' 
ORDER BY table_name, column_name;

If you still encounter an error, you may want to check if you have the necessary permissions to query the information_schema.columns table, or if the table is available in your database.

Anonymous
Not applicable

Hi @Ian Fox​ 

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance! 

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.