How to search informix database for a column

sql informix
zach picture zach · Jul 30, 2012 · Viewed 10k times · Source

I want to be able to search an Informix DB for a column. In the main table there is a cust_nbr column, that is then referenced in a unknown number of tables.

In Informix, is there a way to query the DB and get all the tables that use the cust_nbr?

Answer

zach picture zach · Jul 30, 2012 
SELECT tabname, colno, colname  
FROM systables a, syscolumns b 
WHERE a.tabid = b.tabid 
and colname = "cust_nbr"
ORDER BY colno;

I found this code in the same place and added the extra restriant with colname = cust_nbr.

This seems to have worked for me. i will verify it, but all signs look like it worked.

i found that in the Using the Informix Catalogs mentioned in the other post

Related questions

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints

I make an outer join and executed successfully in the informix database but I get the following exception in my code: DataTable dt = TeachingLoadDAL.GetCoursesWithEvalState(i, bat); Failed to enable constraints. One or more rows contain values violating non-null, unique, …

Read More
Rewrite left outer join involving multiple tables from Informix to Oracle

How do I write an Oracle query which is equivalent to the following Informix query? select tab1.a,tab2.b,tab3.c,tab4.d from table1 tab1, table2 tab2 OUTER (table3 tab3,table4 tab4,table5 tab5) where tab3.xya = tab4.…

Read More
Converting a String to HEX in SQL

I'm looking for a way to transform a genuine string into it's hexadecimal value in SQL. I'm looking something that is Informix-friendly but I would obviously prefer something database-neutral Here is the select I am using now: SELECT SomeStringColumn from …

Read More