Desktop productivity for business analysts and programmers

Column name search

Reply
Occasional Contributor
Posts: 16

Column name search

I have a schema with over 100 tables. I would like to find a column named for example "Teams" and list all the tables where it finds the column name Teams. Can someone help?

Super User
Posts: 9,856

Re: Column name search

This could give you all the column names.

proc sql;
connect to odbc(....);
create table want as
 select * from connection to odbc(ODBC::SQLColumns);
quit;



Occasional Contributor
Posts: 16

Re: Column name search

I know what the column is.. i want to know which tables they are in?

Super User
Posts: 9,856

Re: Column name search

It is simple if you already have a connection to DB.


libname xx odbc dsn=..............;

proc sql;
select *
 from dictionary.columns
  where libname='XX' and name='col_name';
quit;


Super User
Posts: 19,006

Re: Column name search

In addition to @Ksharp code, check the case of the values in the Dictionary table or UPCASE everything. 

 

UPPER()/UPCASE() can be used. 

Super User
Posts: 19,006

Re: Column name search

Most DBs have a dictionary table that contains table and column names. 

 

If your datasets sets are SAS data sets, check SASHELP.vcolumn to find all columns named Team. 

If your dataset are from a DB they may not be listed in SASHELP but the DB should have its own dictionary. 

Ask a Question
Discussion stats
  • 5 replies
  • 236 views
  • 0 likes
  • 3 in conversation