Column name search

[paul2877]

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?

[Ksharp]

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;

[paul2877]

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

[Ksharp]

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;

[Reeza]

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

UPPER()/UPCASE() can be used. 

[JJP1]

HI @Reeza ,

Iam running below query to get to know the column age is present in which dataset under sashelp library ?.
but iam not getting any result in output.even though i can see age column is available.kindly help please.

proc sql;
select * from DICTIONARY.COLUMNS
where LIBNAME = 'SASHELP' and NAME = 'AGE'
;
quit;

[jebjur]

Make sure you use the UPCASE function, as some column names are lowercase:

proc sql;
select * from DICTIONARY.COLUMNS
where LIBNAME = 'SASHELP' and UPCASE(NAME)= 'AGE';
quit;

[JJP1]

WOW.Thanks Jebjur for helping me

[Reeza]

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.