Re: Information about columns with SQL

Thomas Stainer wrote: > I'd like to get a list for some of my db-tables by a simple ;-) > SQL-Statement: Doing it in a single statement is extremely difficult, if not impossible. > This list should contain information like: > tablename, > columname, > rights (what does the "su-idxar"-flags in systabauth really mean: > select, update, ?!?, indexing, delete, exclusive lock, alter table, ?!?) See the manual (Informix Guide to SQL: Reference) and the chapter on the system catalog. su*idxar flags: select update column-level info in syscolauth insert delete index alter reference (make foreign key referencing this table) > data type (integer, varchar, year-to-fraction etc.) Complex -- see manual. > index on column. Not clear what you're after here, but sysindexes contains (most of) the answer, with auxilliary info from syscolumns and systables. > Especially with the data type I'm having problems getting information > about it. I'm running IDS 7.30 UC6. > Any useful help will greatly be appreciated Quite a lot of this stuff is decoded in the SQLCMD source code. In fact, for 7.30, it is complete. SQLCMD doesn't yet manage all the 9.x data types. And you can look at the code for INFO INDEXES, INFO COLUMNS, INFO PRIVILEGES in SQLCMD to see most of the information you are after. Note that the code takes the view that you find the table ID (systables.tabid) and then use that to handle the rest of the lookups. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"